topic Re: Time Duration by Rows in DAX Commands and Tips

Time Duration by Rows

Tan_LC — Fri, 21 Feb 2025 08:51:55 GMT

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,

Re: Time Duration by Rows

bhanu_gautam — Fri, 21 Feb 2025 09:00:14 GMT

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

Re: Time Duration by Rows

rohit1991 — Sat, 26 Jul 2025 05:58:59 GMT

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.

Re: Time Duration by Rows

Tan_LC — Tue, 25 Feb 2025 01:35:58 GMT

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

Thank you.

Regards,