Measure Efficiency help: Calculating the average of a measure evaluated in different contexts

[Casteless]

Hey All,

I'm trying to calculate the average value of a measure [Net Reserve] over the past three months. 

The below code works but is cumbersome, and would not scale well to formulas that involve more variables/bigger time periods. 

Average Net Reserve (3mo) = CALCULATE(

CALCULATE([Net Reserve],PREVIOUSMONTH(Reserve[Month]))+

CALCULATE([Net Reserve],PREVIOUSMONTH(PREVIOUSMONTH(Reserve[Month])))+

CALCULATE([Net Reserve],PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Reserve[Month]))))

)/3)

Any help to optimize this type of calcualtion?

[v-sihou-msft]

@Casteless

According to your description, you want to calculate the moving average for past three months. Right?

In this scenario, you can use DATESINPERIOD() function as filter in CALCULATE() to get the 3 month moving total, then divide by three to get the average.

[3 Month Moving Average] =
CALCULATE(SUM(Table[Net Reserve]), 
          DATESINPERIOD(Reserve[Date], 
                        LASTDATE(Reserve[Date]),-3, Month
                       )
         )
/3

For more details, please refer to blogs below:

Moving Averages, Sums, Etc.

Rolling 12 Months Average in DAX

Statistical Patterns

Regards,

[Casteless]

That calculates the moving average of a column though does it not? [Net Reserve] is a measure in my model.