Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 c...
Need Help
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous 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 ) ) )
    )

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous Can you post sample data as text?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler 

    Numeric_DurationDuration
    0.000:00:00
    0.550:00:06
    0.210:00:12
    0.120:00:18
    0.180:00:24
    0.110:00:30
    0.100: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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 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 ) ) )
    )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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