Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an INCIDENTS table that has an IncidentId and a DateTime.
DateTime,IncidentId
8/1/2018 00:00,Inc01
8/4/2018 00:00,Inc02
8/7/2018 00:00,Inc03
6/7/2019 15:42,Inc04
6/18/2019 00:00,Inc05
7/6/2019 14:55,Inc06
7/22/2019 00:00,Inc07
7/22/2019 00:00,Inc08
7/31/2019 00:00,Inc09
8/5/2019 00:00,Inc10
8/6/2019 00:00,Inc11
11/25/2019 19:38,Inc12
12/2/2019 10:46,Inc13
12/27/2019 08:48,Inc14
1/5/2020 19:00,Inc15
1/18/2020 14:03,Inc16
1/19/2020 13:54,Inc17
1/20/2020 13:56,Inc18
Two things to consider:
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
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?
EDIT:
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?
DiffMonths =
DATEDIFF(
FIRSTNONBLANK(Incidents[Date/Time], 1=1),
LASTNONBLANK(Incidents[Date/Time],1=1),
MONTH)
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |