Time duration is equal 24:00:00 or more does not working

[ruslan88990]

Hello community, me visualize machine's different performance categories events like runtime,planned stoppages,unplanned stoppages and etc using this formula.

Duration_Formatted = FORMAT(SUMX(ADDCOLUMNS('Machine_Performance',"decimalVal",CONVERT('Machine_Performance'[Duration],DOUBLE)),[decimalVal]),"HH:MM:SS")

  Problem is that if duration is equal 24:00:00 the formula inputs 00:00:00 instead and formula can not sum more than 24 hours also. Is it possible to show time as i needed and summarize duration?

[aduguid · Accepted solution]

Sorry about that, try this one 

Duration_Formatted = 
VAR TotalSeconds = SUMX('Machine_Performance', CONVERT('Machine_Performance'[Duration], DOUBLE) * 3600)
VAR RoundedSeconds = ROUND(TotalSeconds, 0) 
VAR Hours = INT(RoundedSeconds / 3600)
VAR Minutes = INT(MOD(RoundedSeconds, 3600) / 60)
VAR Seconds = MOD(RoundedSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

[Anonymous · Accepted solution]

Hi @ruslan88990 ,

Based on the description, try to use the following dax formula.

Seconds = DATEDIFF(Table1[Opened],Table1[resolved],SECOND)

Duration formatted =
var Hour=QUOTIENT(Table1[Seconds],3600)
var Minute=QUOTIENT(Table1[Seconds]-Hour*3600,60)
var Second=Table1[Seconds]-Hour*3600-Minute*60
return
CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(Hour,":"),Minute),":"),Second)

You can also view the following documents to learn more information.

Solved: Calculating time that has more than 24 hours in a ... - Microsoft Fabric Community

Solved: Converting decimal hours to friendly format (D:H:M... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Try this measure 

Duration_Formatted = 
VAR TotalSeconds = SUMX('Machine_Performance', CONVERT('Machine_Performance'[Duration], DOUBLE) * 3600)
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

[ruslan88990]

Hello, thanks for your reply, your measure works fine with all duration values exception of 24 hours, it puts 01:00:00 😃

[aduguid · Accepted solution]

Sorry about that, try this one 

Duration_Formatted = 
VAR TotalSeconds = SUMX('Machine_Performance', CONVERT('Machine_Performance'[Duration], DOUBLE) * 3600)
VAR RoundedSeconds = ROUND(TotalSeconds, 0) 
VAR Hours = INT(RoundedSeconds / 3600)
VAR Minutes = INT(MOD(RoundedSeconds, 3600) / 60)
VAR Seconds = MOD(RoundedSeconds, 60)
RETURN
    FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")