Loading

Format duration in DAX

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:

  1. 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
  2. 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)

Example demonstrating challenges and solution for formatting the duration

Code for Duration format measure

DurationMeasure =
VAR Hours =
    ROUNDDOWN ( SUM ( LogDuration[DurationDecimal] ) * 240 )
VAR Minutes =
    ROUNDDOWN ( MOD ( SUM ( LogDuration[DurationDecimal] ) * 144060 )0 )
VAR Seconds =
    ROUNDDOWN ( MOD ( SUM ( LogDuration[DurationDecimal] ) * 1440 * 6060 )0 )
RETURN
    "" & Hours & ":"
        IF ( ( Minutes ) < 10"0" & MinutesMinutes ) & ":"
        IF ( ( Seconds ) < 10"0" & SecondsSeconds )

This code has been formatted using https://www.daxformatter.com/.

Leave a Reply

Your email address will not be published. Required fields are marked *