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.

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

 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 ) ) )
    )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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