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.

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

      • konstantinos's avatar
        konstantinos
        Memorable Member
        10 years ago

        Greg_Deckler Also variables as so you know evaluated in the initial filter context , and also can be added in a middle of a formula & not only on beginning. Really cool 

         

        Your knowlendge and variables...you can learn us some cool stuff..Time to revise your forecast blof post measures haha

         

    • Stefkus's avatar
      Stefkus
      Frequent Visitor
      9 years ago

      konstantinos nice script, thanks! I used it with SSAS with an extra IF and ISBLANK, works as a charm.... in SSAS.

      However when I import my model in Power BI the numbers shift to the right, hours become minutes, minutes become seconds and seconds disappear. Data Format = Text, Data Type = Auto (Text):

      .

       

      I really don't get this. In Power BI there is nothing to set because I load from a tabular model.

       

      Anyone has an idea if it is possible to get the values in Power BI as hh:mm:ss?

       

      Thanks in advance,

      Stefkus

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        I have the same problem, I can not hit these hours ...

         

         

  • 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