cancel
Showing results for 
Search instead for 
Did you mean: 

Aggregating Duration/Time

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 ticker 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.

Comments

@Greg_Deckler and @konstantinos keep the good stuff coming! Thanks guys!

Added tracking counter.

 

Instead of doing that extensive concatenation, how about that short classic ...

H = RIGHT(100+[Hours],2) M = RIGHT(100+[Minutes],2) S = RIGHT(100+[Seconds],2)

Really Helpful. Especialy working with large times!!

Thanks

Thanks for the great post!

 

I have a two-column table: "Date", and "Average Length of Call in Seconds".  I've created a new column using your code above which works perfectly.  But I end up getting total number of seconds for the Y-Axis labels.

 

Is there any way to show duration for the Y-Axis labels in mm:ss format?  If so, can you point me in the right direction?

 

Thanks for any help!

--Michael--

To vote for having a real Duration data type in Power BI, vote for it here:

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

 

Hello,

 

Thanks for this formula. I've gone through a similar process using Tableau (which also lacks a handy 'time duration' feature). However, I'm not able to run calculations on this since the end result is a concatenated string. How would I display the average Duration? Or, supposing I wanted to show a trend over time. How would I display Duration MoM?

 

Thanks!

So, today you would have to perform the calculations while it is in a number format like total seconds and then convert it at the end. I realize that this does not help with thing like column charts and such. I just returned from the Microsoft MVP Summit in Bellevue and I specifically brought this issue to the attention of the Power BI Desktop team, Gateways and Connectors team and the SSAS Tabular team and believe I found an owner for the issue and so hopefully (fingers crossed) the issue will get sorted out soon. The problem is this issue lives across all 3 teams, the duration data type needs to be supported in Power Query (which it is, Time.Duration) but then also in the tabular data model (not currently supported) and then it would need a supported user interface (formatting) in the Desktop (can't do this until the data model supports it. I had some good discussions with the SSAS Tabular team about how to store it in the data model (as seconds, milliseconds, etc.) So, again, hopefully we will see something soon. Definitely vote for the Idea!

Hi Smoupre,

 

I came across this article for Tableau that produced a calculatable time value. I've almost got it working in PowerBI. Here's the formula:

Time = 
FORMAT(INT(
IF(MOD([Seconds],60)=60,0,MOD([Seconds],60)) + 
IF(MOD(INT([Seconds]/60),60)=60,0,MOD(INT([Seconds]/60),60)*100) +
INT([Seconds]/3600)*10000), "0:00:00")

Basically, it used Modulo to see where the digits should fall and then adds them up. This number is then split with a custom format using colons. It produces values like so:

 

104 = 0:01:44

601 = 0:10:01

43,498 = 12:04:58

 

However, I've only been able to create this formula as a custom Column, not custom measure (likely because my understanding of the difference is lacking), and thus can't run calculations off of it. Thoughts?

Smoupre,

 

Thanks a ton for your review from the MVP Summit, and for your work regarding this issue.  Makes much more sense understanding the bigger picture.  Fingers crossed this will be fully implemented soon.

--Michael--

JWE Regular Visitor

Hi there

 

how to do this with Minutes?

For example:
I have 2.480,08 minutes

I want to format this in duration (but not in Time-Format) because the value is > than 24 hours.

Result shoud be:

1 day 08:22:16

 

But without VAR - this slowdown extremly all my visualizations - isn´t it?

 

How to proceed?

 

thanks in advance Jorg

HI Smoupre,  

 

This helped, although trying to find a easier solution I figured below might help

 

First convert the data into seconds.  Then divide the data by 86400 (60 min x 60 secs x 24 hours) to get output that can be converted to time.  Then use below to get time equivalent

 

FORMAT([seconds]/86400,"Long Time").  This gives time equivalent with AM / PM at end.  You can use Left to trim it. 

 

Left(FORMAT([seconds]/86400,"Long Time"),7)

 

This solves the averaging problem and other time duration related problems. 

 

 

Capture.PNG

Additional Date/Time formats in DAX can be find below

https://technet.microsoft.com/en-us/library/ee634813(v=sql.105).aspx

 

Hope this helps.

 

 

Thanks

Disclaimer - I am new to DAX and PowerBi so my skills are rudemantary.  

 

[removed by me] 

I would like to ask, how can I convert the average time (in decimal) to HH:MM:SS but still it aggregrates the ticket priority?

 

 

Screenshot_1.jpgScreenshot_2.jpg

One additon I would suggest is to add a >0 to the item in the Return If and a BLANK() insetad of "" in the final (false) part of the Return If. this ensures that you don;t end up wiht lots of blank rows in visuals such as tables/matrix.

Thanks for the ideas! As you mentioned, because this is a text string, unfortunately I cannot get it to work with a bar chart. One suggestion, a person can work around the nested CONCATENATE function at the end to avoid the two string limit by doing this instead:

RETURN
  H & ":" & M & ":" & S.

Alopez, I'd calculate the average time in a seperate measure first then convert the measure such as:

AvgTotDurTime = 
VAR Duration = [AvgTotalDurSecs]
VAR Hours = INT ( Duration / 3600)
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))
RETURN
IFERROR( 
IF ([AvgTotalDurSecs]>0, CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )),BLANK())
,BLANK())

The solutions here are okay for a single card value, but as they yield strings they are no good for maths based functionality.

 

It is an astonishing omission from Power BI that there is no Duration type when you create reports.

I agree, what is really needed is a Duration format for numbers.

Hi @Greg_Deckler,

 

What happened to the potential owner of this after the MVP Summit?

 

I had considered using R, but the limitation of needing a Pro license to view an R tile in a report makes it a non-starter.

Unfortunately this options doesn't work for charts like column charts or line charts.

@luissimoes- Agreed, it is a work-a-round. Until they put something like a formatting option for numbers for duration this will continue to be an issue.

This helped loads, thank!

This is taking forever unfortunately 😞

And when you have your column in time format and you want to do the average of this column, but, you're using DirectQuery mode. Time format: HH:mm: ss

Could someone help me?

Thanks, nice article

I believe that all of the functions used are optimized for DirectQuery so you should be able to use them in any DAX formula.

 

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

Yes @Greg_Deckler, you're right!

I said that because of the data modeling, but now I have my answers.
One more thing..

Do you  know how to convert (dd/MM/yyyy HH:mm: ss) into seconds?

Thanks @Greg_Deckler

@ Greg_Deckler...

 

This is amazing.. You have saved me a with this..

 

Dont know why the results of Calculated field in Excel and the results of New Column or measure for Timer function is different on Power Bi,,,

 

But this little tweak...!!! Great.. Thanks a Lot...

Can I get the dax formula to convert   Ex- 2.18 in HH:MM ?

 

NOTE - IF 1 =60 MINS

                

Hi @Greg_Deckler ,

 

I really appreciate your effort. It really helps until we get the duration format.

 

But, I come up with 2 minor issues when I applied this code for my excercise and they are as follows:

  • If the value is blank, it shows "::"

Picture1.png

  • Issue with seconds while rounding up to "60"

Picture2.png

 

And, I think we can improve it with following changes in the code:

  • By adding an if statement to handle the blanks: 
    RETURN
        IF(
            Duration<>BLANK(),
            CONCATENATE (
                H,
                CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
            ),
            BLANK()
        )
  • Instead of rounding up the values, we may round down the values:
    VAR Seconds =
    ROUNDDOWN(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

Complete Code:

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 =
    ROUNDDOWN(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round down 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
    IF(
        Duration<>BLANK(),
        CONCATENATE (
            H,
            CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
        ),
        BLANK()
    )

 

 

 

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

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

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

Fantastic! Thanks so much

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