cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Aggregating Duration/Time

This is an interesting problem, I would post this as a new topic as it will get a lot more exposure that way.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dshah Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time

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

 

dshah Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time

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

Stefkus Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time

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

1.PNG

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

2.PNG.

 

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

ralsouza Regular Visitor
Regular Visitor

Re: Aggregating Duration/Time

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

 

 Capture.PNG

ralsouza Regular Visitor
Regular Visitor

Re: Aggregating Duration/Time

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 ) ) )
    )
agnaldocarmo Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time

It works perfectly, but we need apply only against measure, not against common column!

Thanks

Bwidener Regular Visitor
Regular Visitor

Re: Aggregating Duration/Time

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

Super User
Super User

Re: Aggregating Duration/Time

@Bwidener- https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!