Calculate Average Duration Time (mm:ss)

[rc_stem]

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]

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

[Greg_Deckler]

@rc_stem Can you post sample data as text?

[rc_stem]

@Greg_Deckler 

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 · Accepted 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 ) ) )
    )

[rc_stem]

@Greg_Deckler 

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 :

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 = ([WAIT_TIME_MINUTES - Copy])

// 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 (

          M, CONCATENATE ( ":", S )

    )