Forum Discussion

rautaniket0077's avatar
rautaniket0077
Icon for Resolver I rankResolver I
3 years ago

difference between 2 datetime columns in hr:mm:ss

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_timeend_timeExpected OutputCurrent Output
21-06-2023 11:20:00 AM21-06-2023 11:30:00 AM00:10:0000:10:00
21-06-2023 11:20:00 AM21-06-2023 11:30:00 AM00:10:00 
21-06-2023 11:20:00 AM21-06-2023 11:30:00 AM00:10:00 
22-06-2023 09:10:00 AM22-06-2023 09:30:00 AM00:20:0000:20:00
23-06-2023 10:10:00 AM23-06-2023 10:40:00 AM00:30:0000:30:00
24-06-2023 07:30:00 AM24-06-2023 07:40:00 AM00:10:0000:10:00
25-06-2023 08:00:00 AM25-06-2023 08:50:00 AM00:50:0000:50:00
25-06-2023 08:00:00 AM25-06-2023 08:50:00 AM00:50:00 
25-06-2023 08:00:00 AM25-06-2023 08:50:00 AM00:50:00 



Any help will be appreciated.


15 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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's avatar
      rautaniket0077
      Icon for Resolver I rankResolver I

      Hi tamerj1 ,

      facing below error

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        rautaniket0077 

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

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