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.

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I almost solved the problem, I need to correct the seconds.

     

    _Duration Tickets = 
VAR Duration = [_Total Hours]
VAR Hours = INT (Duration)
VAR Minutes = INT ((Duration - Hours) * 60)
VAR Seconds =  ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );0)

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