Forum Discussion

cmorris's avatar
cmorris
Advocate II
10 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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.

dshah's avatar
dshah
Frequent Visitor
9 years ago

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