topic Re: datediff in HH:MM format in DAX Commands and Tips

datediff in HH:MM format

elads — Thu, 01 Aug 2019 05:44:23 GMT

Hi,

I have two columns:
1. Start time
2. End time.

I want to know the duration of time between the two columns in hour and minute format (HH:MM)

Then I want to display their summary on the "CARD" in the same format.

Attached is a sample example.

Thank you so much for helping.

BR,

Alon

Re: datediff in HH:MM format

Mariusz — Thu, 01 Aug 2019 08:42:24 GMT

Hi elads

You can do something like below, you can adjust the code to show only hh:mm if you wish, but the below will address the issue of days when the sum goes over 24.

Measure = 
VAR _all = SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1
VAR _days  = INT( _all ) 
VAR _time = _all - _days 
RETURN  FORMAT( _days, "#0D-" ) & FORMAT( _time, "hh:mm:ss")

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Re: datediff in HH:MM format

Anonymous — Thu, 01 Aug 2019 08:45:49 GMT

Duration HH:MM:SS = 
// We start with a duration in number of seconds
VAR Duration = DATEDIFF( 'Table'[StartDate], '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 ) ) )
    )

This is code from:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Best

Darek

Re: datediff in HH:MM format

elads — Fri, 02 Aug 2019 07:30:37 GMT

Hi,

Thank you for your answer,

The result is good, but I have to summarize the hours and minutes.
I don't seem to be able to do that because this field is text.

BR,

Alon

Re: datediff in HH:MM format

Mariusz — Fri, 02 Aug 2019 07:46:26 GMT

HI elads

Sorry, but who are you replying too?

Best Regards,
Mariusz

Please feel free to connect with me.

Re: datediff in HH:MM format

Anonymous — Fri, 02 Aug 2019 09:08:48 GMT

Mate, a little bit of thinking goes a long way... If you have to summarize, then the measure should first add up all the seconds and then apply the above formula to this sum. Is this not obvious?

Best
Darek

Re: datediff in HH:MM format

elads — Fri, 02 Aug 2019 14:30:36 GMT

Hi,

Sorry, I answered you..

Re: datediff in HH:MM format

elads — Fri, 02 Aug 2019 14:32:23 GMT

Hi,

Sorry, I answered you Mariusz

Re: datediff in HH:MM format

elads — Fri, 02 Aug 2019 14:34:25 GMT

Hi Anonymous,

Do you have an example of such a calculation ..?

Re: datediff in HH:MM format

Mariusz — Fri, 02 Aug 2019 14:37:56 GMT

Hi elads

The below Measure does the sum ( SUMX part ), the rest is just formatting it to DD - HH:MM:SS, technically you can use SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1 part on its own, however this would give you only decimal value.

Measure = 
VAR _all = SUMX( 'Table', 'Table'[End] - 'Table'[Start] ) * 1
VAR _days  = INT( _all ) 
VAR _time = _all - _days 
RETURN  FORMAT( _days, "#0D-" ) & FORMAT( _time, "hh:mm:ss")

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Re: datediff in HH:MM format

Anonymous — Fri, 02 Aug 2019 16:14:40 GMT

Create a calculated column that for each row will store the number of seconds between the two dates. Then take the measure I've shown you and operate on the SUM of the column. So, you should create a measure like [Total Seconds] = SUM ( T[Seconds] ) and then use its value in my measure replacing the part which is responsible for calculating the seconds...

Best
Darek

Re: datediff in HH:MM format

elads — Fri, 09 Aug 2019 11:42:11 GMT

Hi,

I created a calculated column that sums up the seconds between the two columns

sum_Diff_In_Second = sum(Table_Name[Diff_In_second])

Which part of your code should I replace not so clear ..?

Re: datediff in HH:MM format

Anonymous — Fri, 09 Aug 2019 12:06:54 GMT

"So, you should create a measure like [Total Seconds] = SUM ( T[Seconds] ) and then use its value in my measure replacing the part which is responsible for calculating the seconds..."

Use Its value in my measure replacing the part which calculates the seconds...

I think this is as clear as the sun. Your "effort" is to find the part that calculates the seconds and substitute the value of the measure for the number of seconds. Can't be any simpler than that.

Best

Darek

Re: datediff in HH:MM format

Anonymous — Tue, 29 Mar 2022 10:18:27 GMT

Thnaks for information

Re: datediff in HH:MM format

Anonymous — Fri, 17 Feb 2023 15:02:56 GMT

This was a great solution. Worked like a charm!