Derive value from previous recorded reading for specific ident

[Anonymous]

I would like to create a column that calculates the a value for the ammount of time run between two dates.

IE: (Total run time for ident aa19 on 09/01/2023) - (Total run time for ident aa19 on 02/01/2023)

I cannot get a figure which filters for both the required ident and the previous date recorded.

Any help appreciated.

Capture Date	Ident	Total run time	run time since last capture
02/01/2023	aa19	3332
02/01/2023	aa97	2033
02/01/2023	ab211	3900
02/01/2023	ab20	3655
09/01/2023	aa19	3333
09/01/2023	aa97	2033
09/01/2023	ab211	3907
09/01/2023	ab20	3655
16/01/2023	aa19	3335
16/01/2023	aa97	2048
16/01/2023	ab211	3920
16/01/2023	ab20	3655
23/01/2023	aa19	3347
23/01/2023	aa97	2050
23/01/2023	ab211	3929
23/01/2023	ab20	3659
30/01/2023	aa19	3352
30/01/2023	aa97	2050
30/01/2023	ab211	3937
30/01/2023	ab20	3667
06/02/2023	aa19	3358
06/02/2023	aa97	2050
06/02/2023	ab211	3951
06/02/2023	ab20	3673

[Anonymous]

That worked great,

Thank you very much 👍

[amitchandak · Accepted solution]

@Anonymous , if you need a new column

diff with Last Run =

_last = Maxx(filter(Table, [Ident] = earlier([Ident]) && [Capture Date] < earlier([Capture Date]) ),[Capture Date])

return

_last - Maxx(filter(Table, [Ident] = earlier([Ident]) && [Capture Date] = _last ),[Total Run])

if total run is measure then you need a measure

Diff Measure= [Total Run] - calculate([Total Run], OFFSET(-1, ALLSELECTED('Table'[Ident],'Table'[Capture Date]), ORDERBY('Table'[Capture Date],asc),KEEP,PARTITIONBY('Table'[Ident])))