Forum Discussion
Format Duration int to Datetime D HH:mm:SS
- Anonymous7 years ago
Just to make sure everything is in order. Your original field, which expressed the data in Minutes as a whole number, will be of data type 'Whole Number', but all subsequent fields should be either a decimal number type, or a date/time style number type.
Duration is only a datatype within Edit Queries (Power Query Language, known as M), but is not a data type you can select in the Power BI Data Model itself. Duration is best kept as a decimal number, but you can format it yourself when trying to display on a report. This could be done using another measure and the FORMAT statement, or by using some math tricky to build out a TEXT value to display how you wish.For example:
Display Duration = VAR hours = FLOOR([SumNewDurationINConditoon] * 24, 1) var minutes = FLOOR(([SumNewDurationINConditoon] * 24 * 60) - (hours * 60), 1) RETURN hours & ":" & minutes
I needed the H:mm datatype to represent a duration derived from subtracting Date/time types, but when I tried to use that datatype to create a calculated column-eekkk yikes, Power BI gave me the wrong data. Segments of the 24th hr or something, Work. So I went back to my excel file, made the calculated column from there, re-uploaded and like magic, no datatype conversion issues.