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

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

  • cmorris's avatar
    cmorris
    Advocate II
    10 years ago

    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

    • 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.

      • 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 ) ) )
    )