topic difference between 2 datetime columns in hr:mm:ss in DAX Commands and Tips

difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Mon, 26 Jun 2023 15:10:26 GMT

Hi Team,

I am using direct query mode and I want the difference between 2 datetime columns in hr:mm:ss using DAX
The DAX I am using right now is

Duration HH:MM:SS = 
// We start with a duration in number of seconds
VAR Duration = DATEDIFF( MAX('Table'[StartDate]),MAX('Table'[EndDate]), SECOND )
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

The thing with this my Data is I have same start_time and end_time in multiple rows and as i am using MAX in measure, its not calculating for the entire rows.

Sample Data

start_time	end_time	Expected Output	Current Output
21-06-2023 11:20:00 AM	21-06-2023 11:30:00 AM	00:10:00	00:10:00
21-06-2023 11:20:00 AM	21-06-2023 11:30:00 AM	00:10:00
21-06-2023 11:20:00 AM	21-06-2023 11:30:00 AM	00:10:00
22-06-2023 09:10:00 AM	22-06-2023 09:30:00 AM	00:20:00	00:20:00
23-06-2023 10:10:00 AM	23-06-2023 10:40:00 AM	00:30:00	00:30:00
24-06-2023 07:30:00 AM	24-06-2023 07:40:00 AM	00:10:00	00:10:00
25-06-2023 08:00:00 AM	25-06-2023 08:50:00 AM	00:50:00	00:50:00
25-06-2023 08:00:00 AM	25-06-2023 08:50:00 AM	00:50:00
25-06-2023 08:00:00 AM	25-06-2023 08:50:00 AM	00:50:00

Any help will be appreciated.

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Mon, 26 Jun 2023 16:29:56 GMT

Hi rautaniket0077
You can try

Duration HH:MM:SS = MAXX ( 'Table', 'Table'[EndDate] - 'Table'[StartDate] )

And then select (Long Time) format

Or you can use TIMEVALUE function as follows

Duration HH:MM:SS = TIMEVALUE( MAXX ( 'Table', 'Table'[EndDate] - 'Table'[StartDate] ) )

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Tue, 27 Jun 2023 09:39:54 GMT

Hi tamerj1 ,

facing below error

error -- operator does not exist: inter-timestamp without timezone

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Tue, 27 Jun 2023 09:44:32 GMT

rautaniket0077

Would you please share a screenshot that shows the dax code along with the error

Re: difference between 2 datetime columns in hr:mm:ss

devanshi — Tue, 27 Jun 2023 10:37:14 GMT

TimeDifference = FORMAT([EndTime] - [StartTime], "hh:mm:ss")

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Tue, 27 Jun 2023 13:06:40 GMT

Actually no as report is on client side

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Tue, 27 Jun 2023 13:11:10 GMT

Hi devanshi ,

This with this is, Between start and end date ia have gap of days also.

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Tue, 27 Jun 2023 13:17:55 GMT

rautaniket0077

Ok, then try

Duration HH:MM:SS =
FORMAT ( MAXX ( 'Table', 'Table'[EndDate] - 'Table'[StartDate] ), "HH:MM:SS" )

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Tue, 27 Jun 2023 13:23:59 GMT

Hi tamerj1 ,

Same error as earlier.

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Tue, 27 Jun 2023 13:40:03 GMT

rautaniket0077

Try it this way

Duration HH:MM:SS =
IF (
HASONEVALUE ( 'Table'[StartDate] ),
CONCATENATEX (
'Table',
FORMAT ( 'Table'[EndDate] - 'Table'[StartDate], "HH:MM:SS" )
)
)

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Wed, 28 Jun 2023 05:55:36 GMT

Hi tamerj1 ,

Thank you for the response this measure is working fine but I have some days gap between start and end datetime columns so it's not considering the days gap.

Apart the measure which I mention in my scenario is working but for unknown reasons it's showing blank values against all the unralated data.

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Wed, 28 Jun 2023 08:24:35 GMT

rautaniket0077

Please clarify further

Re: difference between 2 datetime columns in hr:mm:ss

rautaniket0077 — Thu, 29 Jun 2023 09:55:09 GMT

The measure you suggested --

Works fine when difference is less than one day if the difference is greater than one day, the measure is not converting that day into time.

My measure ---

My measure which I have given in the question works fine if I Return h&m&s

But when I return it like

h&":"&m& ":" &s and put it in table,

It shows me all the records in the table which were not there previously.

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Thu, 29 Jun 2023 10:50:40 GMT

rautaniket0077

So it should show as extra hours even if more thsn 24 hours, right?

Re: difference between 2 datetime columns in hr:mm:ss

tamerj1 — Thu, 29 Jun 2023 19:59:08 GMT

rautaniket0077
Please try

Measure2 = MAXX ( 'Table', VAR TotalSeconds = DATEDIFF ( 'Table'[Start], 'Table'[End], SECOND ) VAR Seconds = FORMAT ( MOD ( TotalSeconds, 60 ), "00" ) VAR TotalMinutes = QUOTIENT ( TotalSeconds, 60 ) VAR Minutes = FORMAT ( MOD ( TotalMinutes, 60 ), "00" ) VAR Hours = FORMAT ( QUOTIENT ( TotalMinutes, 60 ), "000" ) RETURN Hours & ":" & Minutes & ":" & Seconds )

Re: difference between 2 datetime columns in hr:mm:ss

ppm1 — Fri, 30 Jun 2023 00:48:05 GMT

Please see this article for how to display >24 hrs.

Calculate and Format Durations in DAX – Hoosier BI

Pat