Credit where credit is due. This is an enhancement of the technique originally posted here by @konstantinos and @smoupre.
A client needed a duration expressed in this format: 2 days, 05:03:52
In other words, Whenever the duration exceeds 86,400 seconds, express that portion in days, then build the remainder in hh:mm:ss format.
This is the DAX I came up with. It assumes a measure with duration in seconds is available. In this case [Total Duration in Seconds]:
Duration =
// Duration formatting : Given a number of seconds, returns a format of "n days, hh:mm:ss"
// We start with a duration in number of seconds
VAR Duration = [Total Duration in Seconds]
VAR Days = INT ( Duration / 86400)
VAR Hours = INT ( MOD( Duration - ( Days * 86400 ), 86400 ) / 3600)
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ), 3600 ) / 60)
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ), 3600 ), 60 ), 0) // We round up here to get a whole number
// Day or Days
VAR D =
IF ( Days = 1,
CONCATENATE ( Days, " day " ),
IF ( Days > 1,
CONCATENATE ( Days, " days " ),
""
)
)
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE(D,
CONCATENATE(H,
CONCATENATE(":",
CONCATENATE(M,
CONCATENATE(":",
S
)
)
)
)
)
