Forum Discussion
Time duration is equal 24:00:00 or more does not working
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?
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")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.
4 Replies
- aduguid
Memorable Member
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")Hello, thanks for your reply, your measure works fine with all duration values exception of 24 hours, it puts 01:00:00 😃
- aduguid
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")
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.
