I have a large data set in which I am attempting to create a heat map that shows the number of activities and events monthly for every year. My data has Start Date and End Date for these activities, but I want to see a heatmap that will show me that the event is "counted" in each month between the start and end date.
Name Start Date End Date
Event 1 9/1/2019 12/15/2019
Event 2 3/4/2019 8/1/2019
Event 3 4/1/2019 4/1/2019
Event 4 12/15/2019 2/16/2020
My question is for ex. Event 1: how do I show "heat" or that the event exists in September, October, November, and December, instead of just seeing it in start date or end date?
I've looked at other forums and seen some answers on using cross joins to help populate the data. Because the data set is extremely large and complex, I'm hoping there's a calculation or solution that doesn't need me to mess or add to the data itself.
I'd recommend a date scaffold with what you're trying to do; however, because you're hesitant about using that technique, perhaps the following Hub for other answers will give you some ideas? Best, Don