Time format greater than 24 hours

[weeksd]

Hi all I am working on a Power BI report  in which I am dealing with time values. My issue is that when I sum up a column and then try to display the total in PBI it truncates the time value to 24 hours or less. The value I am summing is in a decimal format.  For example I have a total of 21.20327546 which should then be displayed as 508:52:43. But in PBI it only shows 08:52:43.

I have tried to use FORMAT([measure], "dd:hh:nn:ss") but this format looses one day in the calculation. 508 hours is 21.166667 days.  But when I display in a card the value I see is 20:04:52:43.  The minutes and seconds are correct but the rest is wrong.

My raw data is :

flow_time

0.0192824074
0.0867708333
0.0994097222
0.1202662037
0.1174421296
0.0367592593
0.0013541667
0.0219444444
0.0866898148
0.2036111111
0.2071296296
0.0450231481
0.0433796296
0.0568287037
0.0667013889
0.1135416667
0.1173726852
19.7597685185

Can any one let me know how to get this to display as 508:52:43 in PBI?

[PC2790 · Accepted solution]

Hey @weeksd ,

Can you once try this:

Required = 
var _sum = sum(TimeTable[flow_time])
VAR hours =  INT( _sum * 24 )
VAR Minutes = MINUTE(_sum)
VAR Seconds = SECOND(_sum)
RETURN
Hours & ":" & Minutes & ":" & Seconds

[Hithahrk]

Hello, I was having the same problem and your solution partially solved it. 

I have a column with some negative duration value (due to a difference between worked time and planned working time) and when these negative value convert to hh:mm:ss the have 1 hour added to the result

Ex : instead of obtaining "-0:55:0" I obtain "-1:55:0"

Is there something to do ?

I tried to an if statement to add one hour to the hour Var if a value is negative but I don't really understand how to write with the variable

[YcnanPowerBI]

I just ran across this same issue and was able to use your solution! Thank you!

[amitchandak]

@weeksd , refer two display code in this blog

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...