Forum Discussion
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_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.
15 Replies
- tamerj1
Community Champion
Hi rautaniket0077
You can tryDuration 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
Resolver I
Hi tamerj1 ,
facing below error
error -- operator does not exist: inter-timestamp without timezone- tamerj1
Community Champion
Would you please share a screenshot that shows the dax code along with the error
- devanshi
Helper V
TimeDifference = FORMAT([EndTime] - [StartTime], "hh:mm:ss")
- rautaniket0077
Resolver I
Hi devanshi ,
This with this is, Between start and end date ia have gap of days also.