I’m working with a dataset that tracks status changes over time, where each record has a RecordID, a VersionNumber, and a LastModified timestamp. I need to calculate the time variance (in hours) between the most recent version of a record and the one immediately preceding it. Since these versions aren't always consecutive in the table, how do I write a DAX measure that "looks back" to find the previous timestamp for that specific RecordID and calculates the difference?
3 answers
To calculate the variance between versions, you need to use DAX variables to capture the current row's context and then filter the table to find the "max" timestamp that is still less than the current one. Here is a robust pattern:
Code snippet
Hour Variance =
VAR CurrentID = SELECTEDVALUE('Table'[RecordID])
VAR CurrentTime = SELECTEDVALUE('Table'[LastModified])
VAR PreviousTime =
CALCULATE(
MAX('Table'[LastModified]),
FILTER(
ALL('Table'),
'Table'[RecordID] = CurrentID &&
'Table'[LastModified] < CurrentTime
)
)
RETURN
IF(
ISBLANK(PreviousTime),
0,
DATEDIFF(PreviousTime, CurrentTime, MINUTE) / 60.0
)
Using MINUTE and dividing by 60 is better than using HOUR directly in DATEDIFF, as the HOUR interval only counts boundary crossings (e.g., 1:59 to 2:01 is "1 hour" but 1:01 to 1:59 is "0 hours").
This works great for individual rows, but what if I have a massive table with millions of rows? Won't that FILTER(ALL('Table')...) logic start to crawl because it has to scan the whole table for every single row in the visual
If you're using this for a "Time in State" report, consider adding an Index column in Power Query first, sorted by ID and Timestamp.
I agree, Michael. If you have an index, the DAX becomes even simpler: you just look for Index - 1 where the ID matches. This avoids the "greater than/less than" timestamp comparisons which can be tricky if two updates happen in the exact same second. It makes your variance calculation much more bulletproof.
Spot on, Thomas. For massive datasets, you should optimize the filter context. Instead of ALL('Table'), use ALLEXCEPT('Table', 'Table'[RecordID]). This tells the engine to only look at rows sharing the same ID, which significantly reduces the search space for the MAX function. Also, ensure your LastModified column is indexed at the source (like SQL) if you're using DirectQuery, as Power BI will have to push that "previous value" logic down to the database.