cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmorris
Frequent Visitor

Aggregating Duration/Time

Hi All,

 

Over the last few months while I've found Power Bi to be a great product that has enabled use to do some truely great things I've really struggled with a few areas of Power Bi namely the aggregation if time.

 

Example (Time Values in Seconds): 

 

 TalkTimeWrapTimeHoldTime   
Agent 1601010   
Agent 2701010   
Agent 160100   
Agent 280105   
Agent 340100   
Agent 1100100   
Agent 3601010   
       
Total4707035 Total Seconds575
     Calls7
     AHT (Seconds)82.14285714
     AHT00:01:22

 

I take my raw data in the query editor get any column (example TalkTime) to duration and then create a simple measure (example sum(TalkTime)) to display a total, I can also add the seconds across Talk Wrap and Hold in a new column in query editor change it to duration to get the total time across all three but for the AHT I need to do a dynamic measure outside of the query editor to divide the total by the number of calls, when ever we introduce this the column changes back to a decimal number and displays as a numerical value with no option to display this total as a time formation H:MM:SS etc

 

Am I missing something, the option to aggregate duration's/times seems to come so naturally in other products but in Power BI I always seem to be having issues.

 

1 ACCEPTED SOLUTION

Sure, I can give a general solution but I have no clue as to what AHT refers to. Average Hours Talked? In any case, simple enough, I've done in a series of steps to make it readable.

 

Given a number of seconds "[Seconds]", create the following columns/measures or what not:

 

Hours = ROUNDDOWN([Seconds]/360,0)

Minutes = ROUNDDOWN(([Seconds]-[Hours]*360)/60,0)

Sec = MOD(([Seconds]-[Hours]*360),60)

H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))

M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))

S = IF(LEN([Sec])=1,CONCATENATE("0",[Sec]),CONCATENATE("",[Sec]))

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

 

Text comes out like 01:03:36 for a value of 576 seconds.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

18 REPLIES 18
nblair
Regular Visitor

Different type of data but the same topic.  I'm completely new to PowerBI so I'm not sure if I should start a new topic or just add on here.

 

Anyway..  I am pulling data from machines that are running.  The data will be recorded every second with a date stamp looking something like this...

 

 

_MachineId_TimeStamp_Value

612/5/2016 3:49:17 PM3
712/5/2016 3:49:17 PM0
1012/5/2016 3:49:17 PM1
312/5/2016 3:49:17 PM3
512/5/2016 3:49:17 PM0
612/5/2016 3:49:18 PM3
712/5/2016 3:49:18 PM0
1012/5/2016 3:49:18 PM1
312/5/2016 3:49:18 PM3
512/5/2016 3:49:18 PM0
612/5/2016 3:49:18 PM3
712/5/2016 3:49:18 PM0
1012/5/2016 3:49:18 PM1
312/5/2016 3:49:18 PM3
512/5/2016 3:49:18 PM0
612/5/2016 3:49:19 PM4

 

 

What I have not been able to find or figure out is how to aggreate across a day. 

 

In other words I would like it to end up something like this..

Machine    time                Len        state

3               7am - 7:36      36 Min      1

3                7:36 - 7:38      2  Min      3

3                7:38  - 8:00    22 Min       1

 

 

thanks in advance... and if someone could let me know if I did it right by replying to a common thread vs creating new I would appreciate it.

 

 

 

Bwidener
Frequent Visitor

Can somebody simply paste the exact DAX that will convert seconds to HH:MM:SS, please?

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
dshah
Frequent Visitor

HI smoupre,  konstantinos

 

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

This is an interesting problem, I would post this as a new topic as it will get a lot more exposure that way.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Greg_Deckler
Super User
Super User

Pain, but any chance that you could go with a string and CONCATENATE the components together?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Hi 

 

Thank you for the responce.

 

Could you provide some additional information on your solution? I have found a few solutions designed for Dax in power pivot that work out the hours minutes and seconds and then concentrate them but this is not very flexiable and does not transfer well to Power BI

Sure, I can give a general solution but I have no clue as to what AHT refers to. Average Hours Talked? In any case, simple enough, I've done in a series of steps to make it readable.

 

Given a number of seconds "[Seconds]", create the following columns/measures or what not:

 

Hours = ROUNDDOWN([Seconds]/360,0)

Minutes = ROUNDDOWN(([Seconds]-[Hours]*360)/60,0)

Sec = MOD(([Seconds]-[Hours]*360),60)

H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))

M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))

S = IF(LEN([Sec])=1,CONCATENATE("0",[Sec]),CONCATENATE("",[Sec]))

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

 

Text comes out like 01:03:36 for a value of 576 seconds.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

HI smoupre,  konstantinos

 

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

 

 @Greg_Deckler Thanks..Amazing..you saved me a lot of time ( if I even found a solution  )..One thing only the Hours are multiply or divided by 3600 & not 360..

 

I used  variables ( not in all versions )on your formulas (filters on same table ) that saved me creating / hiding measures 

 

Test =
VAR Duration =
    AVERAGE ( 'Intranet Activity'[Seconds] )
VAR Hours =
    ROUNDDOWN ( Duration / 3600; 0 )
VAR Minutes =
    ROUNDDOWN ( ( Duration - ( Hours * 3600 ) ) / 60; 0 )
VAR Seconds =
    MOD ( Duration - ( Hours * 3600 ); 60 )
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 CONCATENATE ( H; CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) ) )
Konstantinos Ioannou

@konstantinos nice script, thanks! I used it with SSAS with an extra IF and ISBLANK, works as a charm.... in SSAS.

1.PNG

However when I import my model in Power BI the numbers shift to the right, hours become minutes, minutes become seconds and seconds disappear. Data Format = Text, Data Type = Auto (Text):

2.PNG.

 

I really don't get this. In Power BI there is nothing to set because I load from a tabular model.

 

Anyone has an idea if it is possible to get the values in Power BI as hh:mm:ss?

 

Thanks in advance,

Stefkus

Anonymous
Not applicable

I have the same problem, I can not hit these hours ...

 

 Capture.PNG

Anonymous
Not applicable

I almost solved the problem, I need to correct the seconds.

 

_Duration Tickets = 
VAR Duration = [_Total Hours]
VAR Hours = INT (Duration)
VAR Minutes = INT ((Duration - Hours) * 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
    CONCATENATE (
        H;
        CONCATENATE ( ":"; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) )
    )

@konstantinos - Dude, you just taught me something amazing, how in the world have I never used VAR statements yet!!


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler Also variables as so you know evaluated in the initial filter context , and also can be added in a middle of a formula & not only on beginning. Really cool 

 

Your knowlendge and variables...you can learn us some cool stuff..Time to revise your forecast blof post measures haha

 

Konstantinos Ioannou

Trust me, I hear you, there's so much I want to go back and rewrite now!!

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

It works perfectly, but we need apply only against measure, not against common column!

Thanks

Can I get some thoughts on using the below? 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? My goal is a time value I can aggregate.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors