difference between 2 datetime columns in hr:mm:ss

[rautaniket0077]

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.

[devanshi]

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

[rautaniket0077]

Hi @devanshi , 

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

[tamerj1]

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

[rautaniket0077]

Hi @tamerj1 ,

facing below error

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

[tamerj1]

@rautaniket0077 

Ok, then try

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

[rautaniket0077]

Hi @tamerj1 , 

Same error as earlier. 

[tamerj1]

@rautaniket0077 

Try it this way

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

[rautaniket0077]

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. 

[ppm1]

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

Calculate and Format Durations in DAX – Hoosier BI

Pat

[tamerj1]

@rautaniket0077 

Please clarify further 

[rautaniket0077]

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. 

[tamerj1]

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

[tamerj1]

@rautaniket0077 

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

[tamerj1]

@rautaniket0077 

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

[rautaniket0077]

Actually no as report is on client side