Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
@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
@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
@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 ) ) )
)
I'm not sure how to make the measure work since the only way I've been able to format duration correctly is by using the formula below, which must be text since numbers can't have :
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |