Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Kratchie
Frequent Visitor

Calculate duration from fixed date/time

Hi,

 

I've a column with fixed time which begins from 2000-01-01 00:00:00

If we find 2000-01-02 03:10:10 in that column it means 27h, 10 min, 10 sec in duration. How can I calculate and get it as result? 

 

Have a great day

 

 

1 ACCEPTED SOLUTION

hi @Kratchie 

not sure about your ultimate goal, try this:

Column =
VAR _d = TRUNC( [Duration]-DATE(2000,1,1))
VAR _hms = FORMAT((([Duration]-DATE(2000,1,1))-_d),"HH:MM:SS")
RETURN _d &" Days " &_hms
 
I tried and it worked like this:
FreemanZ_0-1671158159804.png

View solution in original post

9 REPLIES 9
ppm1
Solution Sage
Solution Sage

I assume you are later going to aggregate your durations in a measure, so please see this article for a good way to handle durations as decimals (in days) so you can easily do math and use them in visuals, but then display them as hh:mm, etc. when needed.

Calculate and Format Durations in DAX – Hoosier BI

Pat

Microsoft Employee
Kratchie
Frequent Visitor

Hi @ppm1,

 

This I have done before without any problem when it's two columns with both start/end date+time (split it into 4 columns (start&date, end&date, start&time, end&time) but now I need to get duration from fixed timestamps as I wrote above which isnt that easy. I have googled for different solutions but couldn't find any.

 

FreemanZ
Super User
Super User

Hi @Kratchie 

 

try to create a column with this:

Column = FORMAT([Duration]-DATE(2000,1,1),"HH:MM:SS")
 
i tried and it worked like this:
FreemanZ_0-1671107045573.png

Hi @FreemanZ 

 

Your script works perfectly with hours only. When it's more than 24 hours like 1 day + 6 hour it only shows 6:00

 

Screenshot 2022-12-15 at 15.09.17.png

hi @Kratchie 

not sure about your ultimate goal, try this:

Column =
VAR _d = TRUNC( [Duration]-DATE(2000,1,1))
VAR _hms = FORMAT((([Duration]-DATE(2000,1,1))-_d),"HH:MM:SS")
RETURN _d &" Days " &_hms
 
I tried and it worked like this:
FreemanZ_0-1671158159804.png

This is perfect thank you @FreemanZ 

ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Measure = var _mindate =CALCULATE(MIN('Table (2)'[Column1]),ALL('Table (2)'[Column1]))
var _h =  DATEDIFF(_mindate,MAX('Table (2)'[Column1]),HOUR)
var _m = MINUTE(MAX('Table (2)'[Column1]))
var _s = SECOND(MAX('Table (2)'[Column1]))
return

_h & "h " & _m &"m " & _s &"s"


end result:
ValtteriN_0-1671105220115.png

 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





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

Proud to be a Super User!




Hi @ValtteriN

 

When I created a new visual table and added only DURATION column with your script it worked like a charm. BUT when i'm adding more columns it stopped to work by any reason. Seems that only timestamps with seconds still works partly (only hours + seconds), but when it's whole hour. It doesnt work?! 

 

Screenshot 2022-12-15 at 15.04.29.png

@Kratchie  hmm, 

That seems quite weird. The first things that come to my mind are te following:

1. Are there some other dimension information or slicers affecting the visual? 
2. Is the data format similar in both your example and the data in question

When I tried to re-create this the formula worked well even with whole hours and dates across different months:

ValtteriN_0-1671143147175.png

 







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

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.