Forum Discussion

elads's avatar
elads
Helper III
7 years ago
Solved

datediff in HH:MM format

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 "...
help in dax Function
  • Anonymous's avatar
    Anonymous
    7 years ago
    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
Mariusz's avatar
Mariusz
Community Champion
7 years ago

HI elads 

Sorry, but who are you replying too?

 

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

elads's avatar
elads
Helper III
7 years ago

Hi,

 

Sorry, I answered you Mariusz 

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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.
    Mariusz Repczynski