Trying to display Duration as hh:mm in report - not working for anything over 24hrs

[RayRay]

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? 

[ppm1]

Please also see this article for a flexible way to display calculate and display durations.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[Syndicate_Admin]

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.

[Syndicate_Admin]

Maybe I can help you this link

https://blog.iwco.co/2018/03/28/formato-duracion-power-bi/

Best regards

[v-alq-msft]

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

[Syndicate_Admin]

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.

[v-alq-msft · Accepted 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.

[amitchandak]

@RayRay , I doubt above 24 hrs in a time.Refer if this can help

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

[HotChilli]

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)

[Syndicate_Admin]

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.