Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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])))
That worked great,
Thank you very much 👍
@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])))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |