How To Convert Seconds to Time (hh:mm:ss) with DAX in Power BI Desktop

In this video, I show you how to convert a seconds column into a time column that can be used as an timespan axis.

--

[guilhermeganzarolli]

I've looking for tons of tutorials for converting seconds to hh:mm:ss, when is greater than 24hs, this was the only one that worked!!! You are a genius!!!!

[gondebas]

This DAX one-liner will get you what you want:
FORMAT( DIVIDE( [DurationInSeconds] ), 86400), "HH:mm:ss" )

[blade]

THANK YOU for this! Been trying to get all this seconds data to hours and minutes....for HOURS! This was exactly the formula I needed.

[oladman]

You just made me the coolest power bi developer in my workplace. God bless you real good.

[TiagoMarciano]

Thanks, this conversion helped me a lot!

[chanleaknahang]

this is super helpful while i'm looking for it.
Thanks :)

[cyang]

Thanks for sharing this video Jon. You're a life saver!!! :)

[zoniqvc]

Thank you, finally someting that works perfect.

[monicamurphy]

Awesome! Is there any way to sum or take the average of this data? Mine is registering as a text value.

[lennoxmunyanyi]

Can you help, I need the column to be returned with the date/time data type and not text

[EvaDarknes]

Hey, ty for the info, any idea how to turn the result into a Time format (hh:mm:ss) I actually need to do some calculations within.

I´ve tried to add this as a custom culumn in Power Query M, but as the formula is to complex is not allowing me to

Also tried the "FORMAT( DIVIDE( [DurationInSeconds] ), 86400), "HH:mm:ss")" but as the dirrerence between the dates is larger than 1 day is not working, it just trims the day part appart if the seconds>86400

Any ideas flolkss?? Thank you in advance

[harshalarokade]

Thank you for the solution. Would you be able to plot this duration on Y-Axis and Month-Year or by country on X-axis? I have talk time data that needs to be plotted monthly. But Power BI doesn't allow me to plot the Duration on charts. It converts it to a number by aggregating.

[michaelmedellin]

Hey Jon awesome Video... Any way to get the sum of this column?

[devinrelf]

Any ideas on how to get an average of the results? I need to show an average call duration per employee. The DAX worked in converting the seconds into HH:MM:SS but now I can't average the results.

I tried VAR DURATION = CALCULATE(AVERAGE(....) but that didn't work.

[bjrnaadny]

Hi Jon.
I am grabbing my seconds value
VAR Duration = whitebox_facts[DAX flow seconds]
from a measure called DAX flow seconds as below
DAX flow seconds = DATEDIFF(min(whitebox_facts[M_hms_flow_event]), max(whitebox_facts[M_hms_flow_event]), SECOND)

Something isn´t working as the Duration column is 00:00:00
Any ideas?

[theescaper]

Im getting the following error: The syntaxis for .3600 is wrong. I copied and pasted the code, not sure I am doing wrong...

[visheshjjain]

I have duration in hh:mm:ss...how do I convert it to seconds in number?
E.g 00:01:15 is 75 seconds

[YogeshsinhDnyaneshwarKhebde]

I get error: "Cannot convert value '22141:06:00' of type Text to type Date." where LLSecs is 79707960 Seconds

[eddyjawed]

This is only half the problem.

I already found the same post of the function you found added this on a column of a table next to my 'second' values. The issue I am having is the very crude and basic aggregate functions on the visual card itself that take that particular column which holds the 'duration' values. As soon as I filter on the report the smaller sub section of data from 'Duration' column won't sum or average correctly.

Do you happen to know a way around this problem?

So far my reports can only display cards with time duration values in seconds as its easy to sum or average.

[DAVORGOM]

Thaks. Can we work with milliseconds too? I need to graph runners time (100m race), but power bi doesn´t work in this format hh:mm:ss.00. Do u know how can I do it?