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.

nblair's avatar
nblair
Regular Visitor
9 years ago

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's avatar
Bwidener
Frequent Visitor
8 years ago

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