cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gusly Regular Visitor
Regular Visitor

How do display duration properly?

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.

12 REPLIES 12
BarneyL Regular Visitor
Regular Visitor

Re: How do display duration properly?

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.

 

Super User
Super User

Re: How do display duration properly?

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

 

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

gusly Regular Visitor
Regular Visitor

Re: How do display duration properly?

@BarneyL, 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.

Super User
Super User

Re: How do display duration properly?

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Phil77 New Member
New Member

Re: How do display duration properly?

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

Re: How do display duration properly?

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

dshah Frequent Visitor
Frequent Visitor

Re: How do display duration properly?

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.

 

beerygaz Frequent Visitor
Frequent Visitor

Re: How do display duration properly?

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

beerygaz Frequent Visitor
Frequent Visitor

Re: How do display duration properly?

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 313 members 3,309 guests
Please welcome our newest community members: