Forum Discussion
Calculate Average Duration Time (mm:ss)
I've been able to display time duration with a DAX formula, however I'm not sure how to create a formula to calculate average wait time duration (mm:ss) -if I just use the average function it would calculate it in number format instead of time.
Thanks in advance!
rc_stem Not sure I understand what .55 numeric duration means, is that .55 of an hour? But try this below. PBIX is attached below signature.
Average 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 = AVERAGE('Table'[Numeric_Duration]) * 60 * 60 // 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 ) ) ) )
5 Replies
- Greg_Deckler
Community Champion
rc_stem Can you post sample data as text?
Numeric_Duration Duration 0.00 0:00:00 0.55 0:00:06 0.21 0:00:12 0.12 0:00:18 0.18 0:00:24 0.11 0:00:30 0.10 0:00:36 The data set I'm using will be filtered - 1. most recent day of data such as 2/22/2023 average time duration and 2. the overall average time duration filtering only weekdays
- Greg_Deckler
rc_stem Not sure I understand what .55 numeric duration means, is that .55 of an hour? But try this below. PBIX is attached below signature.
Average 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 = AVERAGE('Table'[Numeric_Duration]) * 60 * 60 // 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 ) ) ) )
update: I was trying to calculate the avg duration with the duration column when I should've been using my original column - duration by minutes (in number format - you multiple by 60 it'll show seconds) - I had to removed one of the *60 at the beginning since the calculation assumes you start with seconds
