cancel
Showing results for
Did you mean:
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.

Proud to be a Datanaut!

Frequent Visitor

## Re: Aggregating Duration/Time

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.

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

Frequent Visitor

## Re: Aggregating Duration/Time

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.

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

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.

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?

Stefkus

Regular Visitor

## Re: Aggregating Duration/Time

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

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

## Re: Aggregating Duration/Time

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

Thanks

Regular Visitor

## Re: Aggregating Duration/Time

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

Super User