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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tihannah
Resolver I
Resolver I

Seconds to Time Format Dax not converting correctly?

Using a commonly shared dax to convert seconds to Hours/Minutes/Seconds. Have used this before, but for some reason, it doesn't seem to be converting correctly? I'm looking at an average call time and this is the example:

 

Total Call Time - 5,791,498 (sec)

Total Calls - 11,252

Avg Call Time = 514.71 sec

 

Just using an online converter, the Avg Call Time converts to 08:58 (rounded up)

But when I use the Dax converter, it's giving me 08:35? Am I looking at this wrong? Below is the DAX.

 

Avg Call_Time =
// 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 = SUM('Table'[call_tm])/SUM('Table'[answered_calls])
// 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 ) ) ))

 

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

using an online converter, the Avg Call Time converts to 08:58 (rounded up)

No, that's not what it converts to. It converts to 8.5785  . It is a decimal value, not a hexagesimal value.  When you multiply .5785 by 60 you get 34.71.  So your conversion equivalent would be 8:35.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

using an online converter, the Avg Call Time converts to 08:58 (rounded up)

No, that's not what it converts to. It converts to 8.5785  . It is a decimal value, not a hexagesimal value.  When you multiply .5785 by 60 you get 34.71.  So your conversion equivalent would be 8:35.

Ahhh...okay thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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