Rolling average of filtered calculation

[JamieH]

I have a calculation that works out the percentage of something for each month (it's plotted against a column called PaymentMonth, which is in a table joined to a standard Date table)

I would like to add a new measure that works out this calculation as a rolling three month average.  So as an example, for PaymentMonth of January 22, I would like it to divide the sum of [amounttofinance] from November, December & January where [Measurement] <> 0 by the sum of amounttofinance from November, December & January.

Can anyone help?

Thanks

MeasurementPct =

Var TotSold = SUM('BackBook'[amounttofinance])

Var TotMeasurement =
Calculate (
SUM ( 'BackBook'[amounttofinance]),
Filter ('BackBook', 'BackBook'[Measurement] <> 0)
)

Return
Divide ( TotMeasurement, TotSold)

[amitchandak]

@JamieH , try like

for three month avg of [MeasurementPct]

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),[MeasurementPct]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

[JamieH]

Thanks Amit.  I think this will give me an average of the averages though, which is what I want to avoid.  

[Richard_100 · Accepted solution]

Hello

It's hard to work this out without direct access to your data model so I have built what I hope is an equivilent problem in the Contoso data, using [Sales Amount] and a filtered version of [Sales Amount] looking at only only those orders with [Net Price] > 1000.  These fields are a stand-in for your [TotSold] and [TotMeasurement] fields.  The key are the variables defining the start and end point of the date range you want

The formula for the last column in the matrix is:

Net Price > 1000 of Total % R3M = 

VAR Date_Endpoint = MAX('Date'[Date])
VAR Date_Startpoint = EDATE(DATE(YEAR(Date_Endpoint), MONTH(Date_Endpoint), 1), -2)
RETURN
DIVIDE(
    CALCULATE([Sales Amount],
              'Date'[Date]>=Date_Startpoint && 'Date'[Date]<=Date_Endpoint,
              'Sales'[Net Price]>1000),
    CALCULATE([Sales Amount],
               'Date'[Date]>=Date_Startpoint && 'Date'[Date]<=Date_Endpoint))

The begining of the month three months ago is put into a variable derived from the MAX of the date in the period shown in the matrix.  Both are then fed into a CALCULATE as normal, which overwrites the filter context of date as displayed in the matrix with the new date range.

I don't believe you need a FILTER function, you should be able to do it all with CALCULATE

I hope this is useful and not too confusing that it is built out of the Contoso data

Regards

Richard

[JamieH]

Thank you Richard.  This is great!