Format hours and minutes

[Anonymous]

Hi everyone,

I have a measure which returns the seconds:

ME_Seconds = CALCULATE(AVERAGE('SLA Cycles'[Seconds]))

I want to convert this to hours and minutes. I used the following measure:

Hours and seconds =

var Seconds=[ME_Seconds]

var Hours =INT(Seconds/3600)

var Minutes = INT(MOD( Seconds - ( Hours * 3600 ), 3600 ) / 60 )

return

Hours* 100 + Minutes

After that, I changed in the Model the formatting to custom format 0:00. 

Unfortunately, I get results like 1:88, but that should be 2:28. So the number goes to 100 instead to 60. 

I hope someone can tell my how to change this or what's going wrong. 

[Greg_Deckler]

@Anonymous I've been seeing this come up lately where there are issues with what is essentially Chelsie Eiden's Duration. Chelsie Eiden's Duration - Microsoft Power BI Community. So, I put together a quick check with seconds running from 0 to 10,000 and cannot replicate the behavior. Everything looks correct in terms of the number of hours, minutes and seconds in the check file so would be interested if you see something different when you open it. File is attached below sig.

[Anonymous]

hey @Greg_Deckler , thanks for your suggestion. Unfortunately, it doesn't work. The minutes and seconds still returns answer >60, so for example -2:73:75 when the correct result should be -2:33

[Greg_Deckler · Accepted solution]

@Anonymous Oh, so negative numbers are wrong. It was never tested with negative numbers. This is what I would do. One, that might be able to be fixed by using TRUNC instead of INT. But, the sure-fire way is this:

Hours and seconds =
Var __Sign = SIGN([ME_Seconds])
var Seconds=ABS([ME_Seconds])
var Hours =INT(Seconds/3600)
var Minutes = INT(MOD( Seconds - ( Hours * 3600 ), 3600 ) / 60 )
return
(Hours* 100 + Minutes) * __Sign

[Anonymous]

Thank you so much @Greg_Deckler ! I have been working on it for a while, and I couldn't find the solution. This works great! 

[Greg_Deckler]

@Anonymous I'm just glad I finally figured out where people were having problems with that code. @konstantinos and I created that code a long time ago and it has pretty much stood the test of time. I will definitely update the Chelsie Eiden's Duration quick measure in my external tool, Microsoft Hates Greg's Quick Measures for the next release! Thanks for your part in improving this code!!

[Anonymous]

@Greg_Deckler that's great to hear! I'm pleased that I was able to help in this way!

[amitchandak]

@Anonymous , Try like

return

quotient([ME_Seconds],3600) & ":" & quotient(mod([ME_Seconds],3600),60) & ":" mod(mod([ME_Seconds],3600),60)

[Anonymous]

Thanks @amitchandak ,

I changed the measure to:

return

QUOTIENT([Tijdsduur remaining (seconden)], 3600) & ":" & QUOTIENT(MOD([Tijdsduur remaining (seconden)], 3600),60) 

Because I only need hours and minutes (not seconds, I am sorry, I named my measure 'Hours and seconds' in the first message, but I meant 'Hours and minutes'. 
However, the results aren't correct yet. But I do know what's wrong. The results of the minutes are reversed, meaning that it shows -117:50, instead of -117:10 or 38:27 instead of 38:33. I tried several things to solve it, without succes. I hope you are able to solve this.