Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need a running total for the latest inspection measurements of distinct units of kit. The inspection frequency for bits of kit varies, so a rolling sum over a fixed time period doesn’t solve my problem.
I have calculated total measurement successfully for today and my calculated table works daily but I need the calculations for past days so I can plot changes over time.
My daily calculation is done as follows:
1) I created a calculated table which gave me the latest inspection measurements for each of the kit units:
Latest_check = GROUPBY('Data’, 'Data’[Kit_ID], "Latest Inspection", MAXX(CURRENTGROUP(), 'Data'[Date]))
2)Added another column which pulls in the latest total defect measurement for that Kit_ID:
Total_defect =
CALCULATE (
MAX ('Data'[Total_Defect]),
FILTER (
'Data’,
'Data'[Kit_ID’] = 'Latest_check'[Data_Kit_ID]
&& 'Data'[Date] = EARLIER ('Latest_check'[Latest check])
3) The output table looks like so:
4) I then summed this table:
5) Total length defect = CALCULATE(SUM('Latest_check'[Total_Defect]))
6) This gives me a total that refreshes every day - so far so good.
7) What I need is for that number to be calculated for every day going back in time so that I can plot Dim[Date] on the x and total length defect over time. I subsequently need rates of change over time - essentially I need to work out whether kit repair is keeping up with rate of breakage.
8)The bits of kit are checked at different time intervals - so I can’t simply look, say over the last x or y days.
What I have tried:
Total_length_defect =
CALCULATE(
SUM(Latest_check[Total_Defect]),
DATESBETWEEN(
'Dim_Date'[Date], BLANK(),
MAX('Dim_Date'[Date])
)
3)Also tried DEFINE table but that didn’t work either – probably because I don’t understand DEFINE and EVALUATION well enough.
4) This calculation does need to run daily, so it is a type of calculated query table, but to be honest it is hard to see where the output of DEFINE “lives” as it isn’t really a measure or a table.
Data model:
And Latest_check is the calculated table which has a join on date.
Attempts and experimentation has progressed.
1) I am now doing my latest_check table more elegantly using:
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
40 | |
19 | |
12 |