cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peppermint_T
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 =

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:

 

Peppermint_T_0-1633101029703.png

 

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 =

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:

 

  • 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.

1 REPLY 1
Peppermint_T
Frequent Visitor

Attempts and experimentation has progressed.

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

Latest check =
FILTER (
'Data',
'Data'[Date]
= CALCULATE (
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 =
CALCULATE(
SUM('Latest check'[Total_length_cracks(mm)]),
DATESBETWEEN(
'Dim_Date'[Date],
TODAY() - 90,
TODAY()
))
 
3) When I plot the Rolling Sum against Dim Date [Date] I get a constant value. The iteration is basically not happening.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!