Skip to content

Instantly share code, notes, and snippets.

@jongio
Created December 1, 2016 23:33
Show Gist options
  • Save jongio/5b8dcf13ab957cecfec3932a42db0e58 to your computer and use it in GitHub Desktop.
Save jongio/5b8dcf13ab957cecfec3932a42db0e58 to your computer and use it in GitHub Desktop.
// 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 ) ) )
)
@TommieA
Copy link

TommieA commented Jan 2, 2023

I have a line chart where I'm trying to show the minutes:seconds in the Y-axis.
image
When I apply the Duration fields that I created, it only allows me to have a count, which is always 1.

Any ideas?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment