Forum Discussion

rc_stem's avatar
rc_stem
Frequent Visitor
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

    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's avatar
rc_stem
Frequent Visitor
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

    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's avatar
      rc_stem
      Frequent Visitor
      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 )
          )