Time aggregation

[Anonymous]

I use the following code and get the time format in format like 3,24(3:24). However,  instead of comma i would like to use - or just plain space, however I would still need to be a able to display it as a value and aggregate it, any ideas?

Test2 = // We start with a duration in number of seconds VAR Duration = DATEDIFF( 'Klaipėda - Radviliškis'[opdata], 'Klaipėda - Radviliškis'[Added Index1.opdata], SECOND ) // 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 // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ",", CONCATENATE ( M, S) ) )

[Greg_Deckler · Accepted solution]

@Anonymous I believe you want the technique in Chelsie Eiden's Duration, a custom display format string. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

This is kind of an update to a older blog post, Aggregating Duration in Power BI: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Custom Format String:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

Also, this might help as well:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

Sorry, have done a lot with duration over the years!!

[Anonymous]

@Greg_Deckler  Hi, you might be able to help me. I need to create new column in the same format as Chelsie Eiden's Duration, but i need all the values to be 04:30:00 in the whole column, so I could compare the actual times to this norm, any way how to edit that code to only get this?

[Greg_Deckler]

@Anonymous Just create a column like:

Column = 43000

Set the Custom Format String to:

0#:0#:0#

[Anonymous]

@Greg_Deckler 

Also, about the time: As I'm aggregating time, but to get the difference between the two columns, I minus each other, however that's what I get, shown below. Why does it not show in hours and minutes?

[Anonymous]

@Greg_Deckler  Many thanks for your help, one more thing: I created a custom column to return the actual time value if the time exceeds the norm and if it did not, then just return the actual time value, like shown below. If the Time column exceeds 4:30, then it shows difference, if not, it returns the actual time value. 

However, i need to display it into the histogram and show the actual value below 4:30 or 4:30 in the green color and then if it exceeds it would add on the column and show it in red, something like that below. Any ideas what measure should be created? 

[Anonymous]

Dude, you're a life saver, can't thank you enough.