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
Trust me, I hear you, there's so much I want to go back and rewrite now!!
th3h0bb5's avatar
th3h0bb5
Resolver II
9 years ago

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.