Created
December 1, 2016 23:33
-
-
Save jongio/5b8dcf13ab957cecfec3932a42db0e58 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Originally from: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 | |
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 = [Change this value to the name of your column that contains your seconds value] | |
// 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, CONCATENATE ( ":", S ) ) ) | |
) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have a line chart where I'm trying to show the minutes:seconds in the Y-axis.
When I apply the Duration fields that I created, it only allows me to have a count, which is always 1.
Any ideas?