There may not be an incident for every month between the first and the last dates.
There may be duplicate DateTime entries (down to the second).
I want to calculate 'Average Incidents per Month', per context. But I want the 'number of months' to be the total number of months in that context regardless of there are incidents for each month in that context.
For instance, if you look at my data, there are 3 instances for the year 2018. And the date of the first incident in 2018 is August 1, 2018. So, my expected calculation should be
AvgIncidents/Month = 3/4 = 0.75
And similarly, since my last incident is on 2020, I should calculate 12 months for year 2019.
For year 2020, my last incident is on January, so I can only count 1 month for 2020.
If we consider the entire table, my number of months should be 4+12+1=17.
Essentially, I guess I'm trying to figure out how to get the number of months between two dates, constrained by the very firs and very last record of my table. Is that possible and how do I do that?
I did come up with a Measure like this which, I guess, gets the job done, but it seems to me a bit ugly. Is there a better way?
You will want to embrace the concept of a Calendar/Dates table. That will help you tremendously, not just with the "dry" periods
Another, related concept you will want to work with is the idea of a Left Join - and the importance of deciding which column from which table in your data model you want to place into your visuals in which order.