daxconvertsecondstotime

// Originally from: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
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 = [Change this value to the name of your column that contains your seconds value]
// 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 ) ) )
    )

Oh my god, thanks so much for posting this! I was desperate to find something that would let me do DD:HH:MM:SS in Power BI and nothing worked. I finally stumbled upon your DAX code above and made a couple alterations to have it calculate days as well. Here's what I wrote:

Duration = 
VAR Duration = 'Test Time Values'[Total Seconds]
VAR Days =
	INT ( Duration / 86400)
VAR Hours =
    INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
    INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0) 
VAR D = 
	IF ( LEN ( Days ) = 1,
		CONCATENATE ( "0", Days ),
		CONCATENATE ( "", Days )
	  )
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        D,
        CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
    ))

Excellent! Thanks for sharing.

Good day,

Tried code but DAX returns with "Unexpected expression CONCATENATE". Please help

Oh my god, thanks so much for posting this! I was desperate to find something that would let me do DD:HH:MM:SS in Power BI and nothing worked. I finally stumbled upon your DAX code above and made a couple alterations to have it calculate days as well. Here's what I wrote:

Duration = 
VAR Duration = 'Test Time Values'[Total Seconds]
VAR Days =
	INT ( Duration / 86400)
VAR Hours =
    INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
    INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0) 
VAR D = 
	IF ( LEN ( Days ) = 1,
		CONCATENATE ( "0", Days ),
		CONCATENATE ( "", Days )
	  )
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        D,
        CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
    ))

@PleZhuR - Do you have a screenshot of the error that you can share?

@PleZhuR - Easy to solve by adding a space just after 1:

VAR D =
IF ( LEN ( Days ) = 1 ,

This is doing what I want it to do, but it doesn't seem to allow you to Sum, or 'Group' the result. Am I missing something here?

I used the first code and am getting some funky results.
The code is ignoring my slicers and providing :: for the full column except for the dates that the slicer selected.
for example I select 7/3/2020 in my slicer
Date handle time
7/4/2020 ::
7/3/2020 00:09:11
7/2/2020 ::
Keeps going for all the dates in the table

@jongio

how to sum the concatenated value

i need to report total calls in days:hour:min:sec format after

Thank you. jongio and PleZhuR. I've tried to solve this issue for most of the day. Now, I can have a weekend.

Thank You so much!!

how to sum the concatenated value

use calculate(average...) in a VAR and so on

I have the Handle time for EACH rows, but how to calculate the Average Handle time?
Also, how to convert the time to mm:ss. Kindly guide me on that

when creating a new column with this code, I cant transform it into Duration. That option is only possible in Power Query afaIk.
I cant make it work to make a new column in Power Query with the code above.
What to do?

Excellence!!! Love what you shared.

@jongio Hi Sir, I follow the way you shared and I got excellent results, but it is still a text type, can we transform it to duration type which is used to calculate the duration in total?

@jongio Hi Sir, I follow the way you shared and I got excellent results, but it is still a text type, can we transform it to duration type which is used to calculate the duration in total?

Hey! Have you solved this problem? I haven't succeeded yet :(

I used the first code and am getting some funky results. The code is ignoring my slicers and providing :: for the full column except for the dates that the slicer selected. for example I select 7/3/2020 in my slicer Date handle time 7/4/2020 :: 7/3/2020 00:09:11 7/2/2020 :: Keeps going for all the dates in the table @jongio @nyincpc

In the RETURN section of the DAX I wrote this to ensure you don't end up with the colons ("::")

RETURN
IF(
CONCATENATE (
D,
CONCATENATE ( " ", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) ))) = " ::",
BLANK(),
CONCATENATE (
D,
CONCATENATE ( " ", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )))
)

@zvyagsta I think the issue might be you've created 'ACD Time' as a column instead of as a measure. When using it as a measure it still shows up as a Text format for me but it still calculates the column when used in a table:

@zvyagsta I think the issue might be you've created 'ACD Time' as a column instead of as a measure. When using it as a measure it still shows up as a Text format for me but it still calculates the column when used in a table:

I tried to create a measure.

A single value for column 'Duration, sec' in table 'data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I tried to create conditions for aggregation SUM, SUMX. I have no result. I'm new at this. What am I doing wrong?

how to sum the concatenated value

use calculate(average...) in a VAR and so on

Could you help me to implement this suggestion? I have a table with some activities and related durations in seconds. I need to calculate the avg of durations and display the value formatted hh_mm:ss in a card. If

how to sum the concatenated value

use calculate(average...) in a VAR and so on

Could you help me to implement this suggestion? I have a table with some activities and related durations in seconds. I need to calculate the avg of durations and display the value formatted hh_mm:ss in a card. If

@EnricaTunesi70, You need to sum the values BEFORE you run it through this measure because the original formula is only used to display the final result as text for visual purposes, not to do calculations.

I have a line chart where I'm trying to show the minutes:seconds in the Y-axis.

When I apply the Duration fields that I created, it only allows me to have a count, which is always 1.

Any ideas?