Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Running total of latest inspection measurements for bits of kit. Kit inspection frequency varies.

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 =


   MAX ('Data'[Total_Defect]),

    FILTER (


        '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:


  1. Using total length defect (as in 5 above) and dragging into a new table OR chart gives me a constant value which is todays value.
  2. This also gave me constant value for each date:

Total_length_defect =




        'Dim_Date'[Date],                                                                                                                                                                                       BLANK(),



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:


  • I have a Data table that captures my data via sharepoint.
  • I have Kit Dimension Table
  • I have a Date Dimension Table.
  • Two simple joins on Kit_ID and Date.

 And Latest_check is the calculated table which has a join on date.

Frequent Visitor

Attempts and experimentation has progressed.

1)  I am now doing my latest_check table more elegantly using: 

Latest check =
MAX ( 'Data '[Date] ),
ALLEXCEPT ( 'Data ', 'Data '[Kit_ID] )
2) The above generates a calculated table that re-calcs every day. I need to sum the Latest check[Total Defect] column over time. I think a 90 day rolling sum might be able to work because the Latest check table is only pulling in the latest value for each kit_ID. In the event of more frequent checks there would therefore be no double counting.  My DAX is:
Rolling Sum =
SUM('Latest check'[Total_length_cracks(mm)]),
TODAY() - 90,
3) When I plot the Rolling Sum against Dim Date [Date] I get a constant value. The iteration is basically not happening.

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors