Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.
I have a column of type Duration which sums up total labor time in the format d.hh.mm.ss
When I try use the data to build a report I can't seem to get it in the format that I need - for the larger values eg 1.20:30 (1 day and 20 hours and 30 min) it needs to be in format 44:30.
example below - Column 1 is the orginal imported (data type = text) Column 2 is transformed data (Duration) I changed the data type to Time, format hh:mm but it just drops the extra 24 hours.
Am I missing something?
Solved! Go to Solution.
Hi, @RayRay
In DAX, you may converts hours, minutes, and seconds given as numbers to a time in datetime format with TIME(hour, minute, second). However, the definition of 'hour' parameter is A number from 0 to 23 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For further information, please refer to the document .
I'd like to suggest you keep the format of (d.h:m:s). You may add a custom column with the following codes as below.
let
text = [Changed Labor],
len = Text.Length(text),
position = Text.PositionOfAny(text,{":"}),
numstart = Number.From( Text.Start(text,position) ),
numend = Number.From( Text.End(text,len-position-1) ),
d = Text.From( Number.RoundDown( numstart/24 )),
h = Text.From( Number.Mod(numstart,24)),
min = Text.End(text,len-position-1)
in
d&"."&h&":"&min
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please also see this article for a flexible way to display calculate and display durations.
Calculate and Format Durations in DAX – Hoosier BI
Pat
I have had the same problem for 2 years and can not be added by several filters in filter and row context, for that format, for that reason I have not used power Bi 100%, if you find the solution please share it.
Maybe I can help you this link
https://blog.iwco.co/2018/03/28/formato-duracion-power-bi/
Best regards
Hi, @RayRay
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
I have that problem too and in consideration of many people who work with time must also have it, I think that POWER BI did a long time, should have solved that problem of the format as shown without problems in Excel. Thank you.
Hi, @RayRay
In DAX, you may converts hours, minutes, and seconds given as numbers to a time in datetime format with TIME(hour, minute, second). However, the definition of 'hour' parameter is A number from 0 to 23 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For further information, please refer to the document .
I'd like to suggest you keep the format of (d.h:m:s). You may add a custom column with the following codes as below.
let
text = [Changed Labor],
len = Text.Length(text),
position = Text.PositionOfAny(text,{":"}),
numstart = Number.From( Text.Start(text,position) ),
numend = Number.From( Text.End(text,len-position-1) ),
d = Text.From( Number.RoundDown( numstart/24 )),
h = Text.From( Number.Mod(numstart,24)),
min = Text.End(text,len-position-1)
in
d&"."&h&":"&min
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Power Query supports the Duration type. PowerBi does not.
If you are just displaying the value, leave it as text.
If you need it as a numeric value, you can use the Power Query functions like Duration.TotalMinutes or search the forum to find a custom formula (there's a few of them in lots of posts)
I think that's not the solution, if power Bi is as advanced as it seems, I think you should have put that format for all summaries in any of the table or chart filters just like Excel itself. Thank you.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the September 2025 Power BI update to learn about new features.