Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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])))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
78 | |
54 | |
39 | |
35 |
User | Count |
---|---|
102 | |
84 | |
48 | |
48 | |
48 |