Time Duration by Rows

[Tan_LC]

Hi, in below table, I would like to calculation the "Duration (mm:ss)" by comparing the DateTime by rows within the same ID.

I've tried on the below method but failed to have the desired solution. Kindly assist.

Duration (mm:ss) =

VAR CurrentDateTime = Table[DateTime]

VAR NextDateTime =

    CALCULATE(

        MIN(Table[DateTime]),

        FILTER(Table,

        Table[DateTime] > CurrentDateTime && Table[ID] = EARLIER(Table[ID]))

    )

RETURN IF(ISBLANK(NextDateTime),

    BLANK(),

    DATEDIFF(CurrentDateTime, NextDateTime, SECOND))

** outcome should be in [mm:ss] instead of [SECOND]. 

ID	DateTime	Duration (mm:ss) (Desired outcome)
103424	20/11/2024 3:27:01 PM
103424	20/11/2024 3:27:26 PM	00:25
103424	20/11/2024 3:27:50 PM	00:24
103424	20/11/2024 3:31:38 PM	03:48
103424	20/11/2024 3:32:05 PM	00:27
102625	20/11/2024 3:35:02 PM
102625	20/11/2024 3:35:02 PM	00:00
102625	20/11/2024 3:35:55 PM	00:53
102625	20/11/2024 3:36:19 PM	00:24
102625	20/11/2024 3:37:55 PM	01:36
101884	20/11/2024 4:24:56 PM
101884	20/11/2024 4:25:44 PM	00:48
101884	20/11/2024 4:36:13 PM	10:29
101884	20/11/2024 4:38:31 PM	02:18
101884	20/11/2024 4:47:56 PM	09:25

Thank you.

Regards,

LC

[bhanu_gautam · Accepted solution]

@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"))

[rohit1991 · Accepted solution]

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.

[Tan_LC]

Hi, may I know if the calculation can be done using New Measure instead of New Column with the same outcome?

Thank you.

Regards,

LC

[bhanu_gautam · Accepted solution]

@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"))