Question

How can I calculate variance in hours between current records and older timestamped data versions?

I have versioned records with timestamps and I want to calculate the difference in hours between the current version and the most recent previous version for each record. I’m looking for a DAX or Power Query approach to calculate this time-based variance efficiently.

Answer 1

Assume you have a table with RecordID, VersionTimestamp, and VersionNumber.

Use EARLIER() or VAR with CALCULATE() to find the previous timestamp per record.

TimeDiffHours =
VAR CurrentRecord = YourTable[RecordID]
VAR CurrentTimestamp = YourTable[VersionTimestamp]
VAR PreviousTimestamp =
    CALCULATE (
        MAX (YourTable[VersionTimestamp]),
        FILTER (
            YourTable,
            YourTable[RecordID] = CurrentRecord &&
            YourTable[VersionTimestamp] < CurrentTimestamp
        )
    )
RETURN
IF (
    ISBLANK (PreviousTimestamp),
    BLANK(),
    DATEDIFF (PreviousTimestamp, CurrentTimestamp, HOUR)
)

Power Query Approach

1. Sort the table by RecordID and VersionTimestamp.

2. Group by RecordID, then:

   • Add an Index column within each group.

   • Use the Index to create a new column that shifts the VersionTimestamp down (previous row).

3. Use a custom column to subtract PreviousTimestamp from CurrentTimestamp in hours:

Duration.Hours([VersionTimestamp] - [PreviousTimestamp])