Forum Discussion

yashrajs's avatar
yashrajs
New Member
2 years ago
Solved

Calculate Average for a measure

I have a table that looks something like this:

 

I use the following formula to caluculate the MEASURE 'Alternative Claculated Burn Rate':

Alternative Calculated Burn Rate = DIVIDE([Count of Contracts], [Sum of Total Time])

Plase note that here Count of Contracts and Sum of Total Time are also measures. 
 
My goal is to create another measure that will hold a single scalar value and will be the average of 'Alternative Calculated Burn Rate' for the last six months. I don't want to have this average for every month, I just want one average calculated using the latest six months. 
 
 
I have already tried using :
Alternative Burn Rate MA = CALCULATE(SUMX(
        TOPN(
            6,
            'Historical Contracts by Load Date',
            'Historical Contracts by Load Date'[Start of Month Date], DESC
            ),
            [Alternative Calculated Burn Rate]
        )/6
    )
This doesn't seem to work correctly. 
 
All help is appreciated, thank you in advance!
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi yashrajs 

    You can consider to create a date table, then create one-many relationship between tables

    Then use the following measure.

    Measure =
CALCULATE (
    AVERAGE ( 'Historical Contracts by Load Date'[Alternative Calculated Burn Rate] ),
    DATESINPERIOD ( 'DateTable'[Date], MAX ( 'DateTable'[Date] ), -6, MONTH )
)

    and you can refer to the following link about the function.

    DATESINPERIOD function (DAX) - DAX | Microsoft Learn

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yashrajs 

    Try the following measure.

    Alternative Burn Rate MA =
CALCULATE (
    DIVIDE (
        SUMX (
            TOPN (
                6,
                ALLSELECTED ( 'Historical Contracts by Load Date' ),
                'Historical Contracts by Load Date'[Start of Month Date], DESC
            ),
            [Alternative Calculated Burn Rate]
        ),
        6
    )
)

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • yashrajs's avatar
      yashrajs
      New Member
      2 years ago

      Thanks for the suggestion Anonymous  however this doesn't seem to work correctly. It gives an insanely high value.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yashrajs 

    You can consider to create a date table, then create one-many relationship between tables

    Then use the following measure.

    Measure =
CALCULATE (
    AVERAGE ( 'Historical Contracts by Load Date'[Alternative Calculated Burn Rate] ),
    DATESINPERIOD ( 'DateTable'[Date], MAX ( 'DateTable'[Date] ), -6, MONTH )
)

    and you can refer to the following link about the function.

    DATESINPERIOD function (DAX) - DAX | Microsoft Learn

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.