Calculate Duration in hh:mm:ss

[Bwidener]

I am trying to figure out how to format time from either seconds, minutes, hours or serial number to HH:MM:SS format that will calculate in charts and tables. I have read a lot of the previous post on the issue but there doesn't seem to be a very detailed way to do this. I am new to Power BI.

// We start with a duration in number of seconds
VAR Duration = [Duration in Seconds]
// 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 ) ) )
    )

It doesn't really explain, in detail, how to convert seconds to duration, it doesn't explain if this needs to be a new column or a new measure. Very confusing. So, if somebody could just post the formula to convert seconds, (seconds are formated at decimal number, however, I can change it to whatever it needs to be), to HH:MM:SS, that would be awesome. 

[Anonymous · Accepted solution]

The DAX you posted can convert number into HH:MM:SS format. It requires to use a calcuated column for running the formula. See the example below.

[v-juanli-msft · Accepted solution]

Hi @Bwidener

Additionally, it should be noted that when you change duration to HH:MM:SS, it would become text type and can't be calculated.

Best Regards

Maggie

[AnneC]

Hiya

I worked out how to convert a number of seconds when it is in a file as a whole number to hh:mm:ss format. You need to use a new column from the modelling tab, then use the following formula:

Column name = RIGHT ( "0" & INT ( ‘File’[Sheet Name] / 3600 ), 2 )

     & ":" &

RIGHT (  "0" & INT ( ( ‘File’[Sheet Name] - INT ( ‘File’[Sheet Name] / 3600 ) * 3600 ) / 60 ), 2 )

    & ":" &

RIGHT ( "0" & MOD ( ‘File’[Sheet Name], 60 ), 2)

This will return a string of whole numbers in seconds, ie 4789 as 01:19:45

Good luck!

[EvgeniyIvanov]

Hi everyone,

the solution that I made work for me to create (Score) Card visualizations  of Elapsed/ Duration Times in HH:MM:SS format capitalizes on AnneC's formula but takes a different approach (Do Measures First then Format in HH:MM:SS) with the following logic:

1. In the Power Query Editor - Ensure your  elapsed times/ duaration times data is calculated in SECONDS and the Column containing that data is of Data Type: Whole Number (Transform tab); 

2. As it is a Score Card it has to be based on a calculation/measure first, e.g. median, average, percentile, etc. Otherwise, it won't work. In my case, I use 90th percentile  for which I utilize the formula: Measure = 

PERCENTILE.INC(QueryName[ElapsedTime in Seconds], 0.9). 

3. Opposite to AnnC's approach, I am creating Measure instead of a New Column to calculate the 90th percentile (or average, or median, etc.). This is how I keep the data format being number/int and thus allows for calculations. Otherwise, a text format won't allow this.

4. Then, I create another Measure on the top of the 90th percentile Measure exploring the formula provided by AnnC using the follwing formula:

90th TOT 1stDispatchedTo1stEnroute Seconds = RIGHT ( "0" & INT ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) / 3600 ), 2 )

& ":" &

RIGHT ( "0" & INT ( ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) - INT ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9) / 3600 ) * 3600 ) / 60 ), 2 )

& ":" &

RIGHT ( "0" & MOD ( PERCENTILE.INC(QueryRedNMxSOCsResponseTimes[TOT 1stDispatched To 1stEnrouteTime Seconds], 0.9), 60 ), 2)

You can see the 90th percentile of the elapsed/duration times represented into an HH:MM:SS format by the Score Cards in the middle of the canvas. Try it and I hope it will work for you as well. Thanks.

[v-juanli-msft · Accepted solution]

Hi @Bwidener

Additionally, it should be noted that when you change duration to HH:MM:SS, it would become text type and can't be calculated.

Best Regards

Maggie

[Bwidener · Accepted solution]

What good is a time calculation if you can't calculate it? I will accept this as a solution under the pretense that you guys are working on a way to do time calculations. Thanks for your help as always. 

[v-juanli-msft]

Hi @Bwidener

How would you like to calculate?

If you want to convert a time in text format to a time in datetime format, you can use the following function

TIMEVALUE

Best Regards

Maggie

[Anonymous · Accepted solution]

The DAX you posted can convert number into HH:MM:SS format. It requires to use a calcuated column for running the formula. See the example below.