DAX Duration Measure in Days, Hours, Minutes, Seconds

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 
 ) 
 ) 
 )
 )
 )
This entry was posted in Analysis Services, Business Intelligence, DAX, SQL Server. Bookmark the permalink.

Leave a Reply

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