Aggregating Duration/Time

[Greg_Deckler]

This is a collaborative blog post by @konstantinos and @Greg_Deckler resulting from the forum discussion topic “Aggregating Duration/Time”. http://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358

Introduction

Both Excel and DAX lack support for a true time duration data type making the display of duration in a format such as “HH:MM:SS” problematic. However, with some creative formulas, this problem can be solved.

Scenario

Consider a scenario where you have a number of seconds worked on a help desk ticket or the duration of a phone call in number of seconds. You desire to put this into a more standardized time duration format such as “HH:MM:SS”.

Here is a small sample of data to work with:

Item,Duration in Seconds

Phone Call 1, 45

Phone Call 2, 2875

Phone Call 3, 8944

Excel

In Excel, we would have the Item descriptions in column 1, A and Duration in Seconds in column 2, B. We could create the following additional columns to solve the problem:

• Hours (C column): = INT(B2/3600)
• Minutes (D column): = INT(MOD(B2,3600)/60)
• Seconds (E column): = MOD(MOD(B2,3600)/60)

However, these calculations do not account for leading zeros and we might end up with a duration of “4:2:8” for four hours, two minutes and eight seconds. Not exactly what we want. So, we have to add more columns:

• H (F column): =IF(LEN(C2)=1,CONCATENATE("0",C2),CONCATENATE("",C2))
• M (G column): =IF(LEN(D2)=1,CONCATENATE("0",D2),CONCATENATE("",D2))
• S (H column): =IF(LEN(E2)=1,CONCATENATE("0",E2),CONCATENATE("",E2))

For Duration, we cannot use the TIME function of Excel as this would return a point in time, such as 12:47 AM. Therefore, we must use CONCATENATE:

• Duration (I column): =CONCATENATE(F2,":",G2,":",H2)

Now we have the Duration in the desired format. Unfortunately, we also have six extra columns cluttering things up. We could hide those columns in the Excel interface or we might even combine everything into a single formula:

• Duration: =CONCATENATE(IF(LEN(INT(B2/3600))=1,CONCATENATE("0",INT(B2/3600)),CONCATENATE("",INT(B2/3600))),":",IF(LEN(INT(MOD(B2,3600)/60))=1,CONCATENATE("0",INT(MOD(B2,3600)/60)),CONCATENATE("",INT(MOD(B2,3600)/60))),":",IF(LEN(MOD(MOD(B2,3600),60))=1,CONCATENATE("0",MOD(MOD(B2,3600),60)),CONCATENATE("",MOD(MOD(B2,3600),60))))

If you are a fan of this second option, you have likely spent far too much time writing Perl code and need to seek professional counseling.

The problem is that both of these options are sub-optimal. When someone else looks at what has been done, or even if you were to look at this 6 months from now, you would essentially have to reverse engineer what has been done, which could take a significant amount of time and troubleshooting. For example, what if one of the “3600” had been mistakenly entered as “360” and was causing a problem? Tracking that down could take some time.

DAX

There are so many similarities between DAX and Excel that our functions are nearly identical. Given the same data and the columns [Item] and [Duration in Seconds], our initial formulas are identical other than the naming convention for the cell/column:

• Hours = INT([Duration in Seconds]/3600)
• Minutes = INT(MOD([Duration in Seconds],3600)/60)
• Seconds = MOD(MOD([Duration in Seconds],3600),60)
• H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))
• M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))
• S = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))

One difference between DAX and Excel however is that DAX’s CONCATENATE function only accepts two arguments, so the final Duration column formula becomes:

• Duration = CONCATENATE([H],CONCATENATE(":",CONCATENATE([M],CONCATENATE(":",[S]))))

Just like Excel, we could combine all of those formulas into a single massive formula, but we have no desire to make your eyes bleed. Thus, we are left in the same predicament as Excel when it comes to maintainability, supportability and overall readability of the solution. Or are we?

A Better Solution in DAX

Luckily, the powerful language-like features of DAX, comments and variables, help us improve our solution dramatically.

Consider the following DAX “formula”:

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// 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 ) ) )
    )

Conclusion

The techniques shown here allow us to solve the problem of the lack of a true time duration data type in both Excel and DAX. Ultimately, DAX allows us to create a superior solution due to its support for powerful programming-language-like features such as comments and variables without resorting to a completely different programming language such as VBScript.

[Anonymous]

Hi @Greg_Deckler ,

Thanks for your post on Time calculation and its really helpful. 

When you calculate time with the format of HHMMSS DAX converts it into String format which cannot be used as a value in any of the PBI graphs. Is there any workaround to this?

Also, what is the best visual to use to reflect duration taken between two entities?

@sirros_iot @jabir_hussain 

Keep up the good work!

Thank you.

Regards,

Las

[Anonymous]

Well, Excel has a feature that does the stuff in one line. Just format the difference between two datetimes with the following string "[hh]:mm:ss" and you're done. No stunts needed.

Here's an example:

=TEXT(B1-A1, "[hh]:mm:ss")

where B1, A1 store datetime values like, say, 2000-01-01 22:00:00.

Best

Darek

[Chris99]

Can't believe we're still waiting for a proper duration data type so many years down the line.

[beatz62]

Fantastic! Thanks so much

[Wkbdrguy]

This formula with the duration works great! Has anyone had an issue with filter/slicer for the duration time to update in a card that is showing the average? Say you filter for the last 2 months. I'm not seeing the duration update but its just staying the same?

8/25 Update

Figured out my issue if anyone is interested

https://community.powerbi.com/t5/Desktop/Need-Help-with-Time-calculation-and-filters/m-p/770074#M371...

[Anonymous]

@Greg_Deckler 

Why is the matrix not adding up the duration?

[brianbarito]

Hi Greg,

Really appreciate your work!

But I have some problem here,

The total time still separated each other and didn't sum up the total time, even though I use the slicer.

Do you have any suggestions?

Thanks a lot!

[Anonymous]

If you want the output in a number format to be able to use the value in a chart you can skip the concatenate part of the formula and in the return statement instead use the Hour, Minute and Seconds variables like below:

RETURN

Hour+Minutes+(Seconds/100)

[reportuser]

When used in a matrix it gives the first or the last instances, what is the work around for that?

[brandongomez]

New here and thank you for posting that formula for conversion! One question....I'm converting milliseconds so would I only need to change the first part of the formula for VAR Hours? From 3600 to 3600000. Or, would I have to change everywhere I see 3600 to 3600000?

[LiziM]

Hi there 🙂 Thanks for this @Greg_Deckler ! Very helpful since I work from home so I can't use the Power Query Editor as I don't have access to our MySQL DB.

But I was wondering, do you know how I could change the data type so that I could plot graphs showing for example, Time To Repar by Machine?

At the moment the data is in text format so I can't do this, and when I try formatting it as time, it expects the highest "hour" value to be 23...

[Greg_Deckler]

@LiziM Can't be completely certain of your situation, but see Chelsie Eiden's Duration:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[AlexisOlson]

Y'all are making this overly complicated.

The FORMAT function can do most of the work here:

Duration = 
VAR _Seconds = [Sec]
VAR _Hours = INT ( _Seconds / 3600 )
VAR _Remainder = ( _Seconds - _Hours * 3600 ) / 3600 / 24
RETURN
    FORMAT ( _Hours, "00:" ) & FORMAT ( _Remainder, "nn:ss" )

Note: FORMAT assumes durations are in units of days, so a seconds --> days conversion is included in the _Remainder variable.

[samhunter106]

This is great @Greg_Deckler. It works great for positives but trying to do this in a variance field including negatives.

I have managed to get it working by adding to the script (below) but wonder, is there is a better solution?

RunDurationVariance =

// Duration formatting

// * Given a number of seconds, returns a format of "hh:mm:ss"

//

// We start with a duration in number of seconds

VAR Duration = if(sum(runs[RunDurationVariance])<0,sum(runs[RunDurationVariance])*-1,sum(runs[RunDurationVariance]))

// 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 H2 =

    IF ( LEN ( Hours ) = 1,

        CONCATENATE ( "0", Hours ),

        CONCATENATE ( "", Hours )

      )

VAR H = if(sum(runs[RunDurationVariance])<0,CONCATENATE( "-",H2),H2)

// 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 ) ) )

    )

[ankush9646]

@Greg_Deckler I have same scenario as posted above but what if there is blank data in any of the column in that case there are no values so it's passing colon only.
How we can handle that scenario if there is blank data then instead of passing : put it blank and if there is any data then concatenate it.
AvgHold =
// Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [AvgHoldTime]
// 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 ) ) )
)