Time Duration by Rows

Tan_LC , Try using

DAX
Duration (mm:ss) =
VAR CurrentDateTime = Table[DateTime]
VAR NextDateTime =
CALCULATE(
MIN(Table[DateTime]),
FILTER(Table,
Table[DateTime] > CurrentDateTime && Table[ID] = EARLIER(Table[ID]))
)
VAR DurationInSeconds = DATEDIFF(CurrentDateTime, NextDateTime, SECOND)
VAR Minutes = QUOTIENT(DurationInSeconds, 60)
VAR Seconds = MOD(DurationInSeconds, 60)
RETURN IF(ISBLANK(NextDateTime),
BLANK(),
FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00"))

Hi Tan_LC ,

To calculate the duration (mm:ss) by comparing the DateTime values between rows within the same ID in Power BI, you need to adjust your DAX formula so that the difference is calculated in seconds and then formatted into minutes and seconds.

Here’s how you can modify your DAX formula:

Duration (mm:ss) = 
VAR CurrentDateTime = Table[DateTime]
VAR NextDateTime =
    CALCULATE(
        MIN(Table[DateTime]),
        FILTER(
            Table,
            Table[DateTime] > CurrentDateTime && Table[ID] = EARLIER(Table[ID])
        )
    )
VAR DurationInSeconds = 
    IF(
        ISBLANK(NextDateTime),
        BLANK(),
        DATEDIFF(CurrentDateTime, NextDateTime, SECOND)
    )
VAR Minutes = QUOTIENT(DurationInSeconds, 60)
VAR Seconds = MOD(DurationInSeconds, 60)
RETURN
    IF(
        ISBLANK(DurationInSeconds),
        BLANK(),
        FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
    )

 

This will give you the desired output of the duration in mm:ss format between rows within the same ID.