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

[Peppermint_T]

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:

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.

[Peppermint_T]

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.