I’ve looked for a way to format duration in a Power BI dashboard. This is what I came up with after some browsing and tinkering.
Two problems to address:
- When having a duration between two date times; where the duration is more than 24 hours, formatting goes awry and you’ll loose the days
- Duration is not aggregated
The solution is in creating a (hidden) column for the duration as a decimal (DurationDecimal); and a measure for formatting this as a readable text (DurationMeasure)
Code for Duration format measure
VAR Hours =
ROUNDDOWN ( SUM ( LogDuration[DurationDecimal] ) * 24; 0 )
VAR Minutes =
ROUNDDOWN ( MOD ( SUM ( LogDuration[DurationDecimal] ) * 1440; 60 ); 0 )
VAR Seconds =
ROUNDDOWN ( MOD ( SUM ( LogDuration[DurationDecimal] ) * 1440 * 60; 60 ); 0 )
"" & Hours & ":"
& IF ( ( Minutes ) < 10; "0" & Minutes; Minutes ) & ":"
& IF ( ( Seconds ) < 10; "0" & Seconds; Seconds )
This code has been formatted using https://www.daxformatter.com/.