Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Greg_Deckler
Super User
Super User

Duration in DAX

With all credit to @konstantinos for turning ugly DAX into elegant DAX, this method turns something like a duration in seconds into a nice, readable HH:MM: SS format (space added because of smiley problems).

 

Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = [Duration in Seconds]
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// 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 (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
1 REPLY 1
Anonymous
Not applicable

This was huge for me (and to learn about Variables in a DAX statement), but I struggled to get the blanks out for data rows which had no data - even after attribute filter was applied.  This change at the end seemed to work in terms of setting it to no data  (I'm not showing seconds) - but this has the behavior of null versus a blank for rows not qualified  .. or this was showing up as blank - ".:"

 

- thanks for sharing!!  And hopefully, my shorthand add helps someone else going down this road.

 

VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( ".0", Hours ),
IF ( LEN ( Hours ) > 1,
CONCATENATE ( ".", Hours ), CONCATENATE ( "", Hours )
))
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( ":0", Minutes ),
IF (
LEN ( Minutes ) > 1,
CONCATENATE ( ":", Minutes ), CONCATENATE ( "", Minutes )
))
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
IF( Minutes > 0 , CONCATENATE (Days, CONCATENATE (H, M) ), Minutes )

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.