cancel
Showing results for 
Search instead for 
Did you mean: 

Chelsie Eiden's Duration

Super User
263 Views
Highlighted
Super User
Super User

Chelsie Eiden's Duration

Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old.  Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with @konstantinos ages ago.

 

So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you.

 

Chelsie Eiden's 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 = SUM([Duration])
// 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
RETURN
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds

All but the last line is the code from that article that @konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!!

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/#customForm...

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!