How do display duration properly?

[gusly]

I'm displaying durations in some of my charts. However, I haven't figured out how to do it in a proper way.

Currently I'm using a column that represent minutes as a decimal number. Not optimal, since I can display values like "5.85". Can I instead create a calculated column that is of type Duration or similar? I want to be able to display "5 minutes 51 seconds". However, I don't want it to be of type Text, I want to be able to sort it, show averages and such stuff.

I found this:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/
...and some threads on this forum, but nothing really that directly tells me how to do it.

[dshah]

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.

[beerygaz]

This doesn't work for durations lower than 3600 seconds, however, as you end up with 12:mm:ss rather than 0:mm:ss

[luigidlt]

Is there any way to make it work for durations lower than 3600. Otherwise I get 12hr in all durations.

Thanks

[thehy]

Hi,

There is a much easier way to do this without DAX or code; with the latest version of PowerBI Desktop at least.

1. Convert the column(s) to 'Duration'

2. This should give you the duration in the format dd.hh:mm:ss.ms

3. Use the edit query 'Extract' -> 'Between delimiters'

4. Enter '.' as the start delimiter and ':' as the end delimiter

5. For the start, scan 'From the end of the input' and skip 2 delimiters

6. For the end, scan 'From the start delimiter, toward the end of the input' and skip 1 delimiter

And that should do it. It works for less that 3600 secs as I have durations less than an hour and more than a day.

[Psimon]

now the column is transformed as text and not as a value!?

[kenwilsonii]

use duration.fromtext, There are built in functions to convert to Duration

[beerygaz]

This doesn't work for durations lower than 3600 seconds, however, as you end up with 12:mm:ss rather than 0:mm:ss

[mcolb88]

As a calculated column:

VAR Minutes = ROUNDDOWN([CallDurationSeconds]/60,0)

VAR Seconds = ROUNDUP(([CallDurationSeconds] - Minutes*60), 0)

RETURN IF([CallDurationSeconds]>0, CONCATENATE(CONCATENATE(FORMAT(Minutes, "##0"), ":"), RIGHT(CONCATENATE("0", FORMAT(Seconds, "##")),2)), BLANK())

And as a formatted measure using the numeric equivalent.

Avg Call Duration:= AVERAGEX('Interview Call', [CallDurationSeconds]/60)

Avg Call Duration (min):=

VAR Minutes = ROUNDDOWN([Avg Call Duration],0)

VAR Seconds = ROUNDUP(([Avg Call Duration] - Minutes)*60, 0)

RETURN CONCATENATE(CONCATENATE(FORMAT(Minutes, "##0"), ":"), RIGHT(CONCATENATE("0", FORMAT(Seconds, "#0")),2))

Could add hours to this as well, but that's pretty far out of range for my application.

Hope this helps

[Greg_Deckler]

I have a blog article developed in collaboration with @konstantinos on this exact subject! It is currently in "staging". The inspiration for it was:

http://community.powerbi.com/t5/Desktop/Aggregating-Duration-Time/m-p/13350/highlight/true#M3358

[Anonymous]

You should be able to add a claculated column to convert to a time.

A quick experiment gives me this formula to take a duration in minutes and turn it into a time (using time as the field with the minutes in):

=TIME(FLOOR([Time]/60,1),FLOOR(MOD([Time],60),1),MOD([Time],1)*60)

this will probably not work for times above 24 hours though, I'd expect them to wrap aroung to 0:00 at that point.

[gusly]

@Anonymous, but how can I use such a value in column (bar) chart? As I can see it I can't use values of Data Type Time, I can only count such values. So even though the conversion works, I'm afraid it doesn't help me.

@Greg_Deckler, I'm looking forward to that article. Please let me know where to find it once released.

[Greg_Deckler]

@gusly - The article was published, you can find it here: http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

[Phil77]

Hi,

thanks for the blog post, helped me a lot to finally use Power BI for my flight book.

Using this calculation works for me in the table visual, but somehow not in a column chart.

As a workaround I have to use a duration in seconds or minutes for the chart, but this makes the axis numbering confusing as it shows thousands of seconds, or houndreds of minutes.

Do you have any tips to make the chart display hours:minutes on the axis?

Best regards,

Phil

[mmay]

Did you find your answer to this? I am looking to display duration in hours or days