Reply
Highlighted
Frequent Visitor
Posts: 7
Registered: ‎09-01-2015
Accepted Solution

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:MMSmiley FrustratedS 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.

 


Accepted Solutions
Super User
Posts: 9,272
Registered: ‎07-11-2015

Re: Aggregating Duration/Time

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 9,272
Registered: ‎07-11-2015

Re: Aggregating Duration/Time

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 7
Registered: ‎09-01-2015

Re: Aggregating Duration/Time

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

Super User
Posts: 9,272
Registered: ‎07-11-2015

Re: Aggregating Duration/Time

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Senior Member
Posts: 360
Registered: ‎06-25-2015

Re: Aggregating Duration/Time

 @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
Super User
Posts: 9,272
Registered: ‎07-11-2015

Re: Aggregating Duration/Time

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Senior Member
Posts: 360
Registered: ‎06-25-2015

Re: Aggregating Duration/Time

@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
Super User
Posts: 9,272
Registered: ‎07-11-2015

Re: Aggregating Duration/Time

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Regular Visitor
Posts: 40
Registered: ‎11-11-2016

Re: Aggregating Duration/Time

[ Edited ]

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.

Frequent Visitor
Posts: 10
Registered: ‎12-06-2016

Re: Aggregating Duration/Time

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.