Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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])))
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |