(12-month) Rolling Average Column

[ChemEnger]

I have tried a load of different combinations and nothing seems to work!

I have data like

Batch Number	Filling Start Date	Total Filled	Waste Fraction
20334	07 Nov 2020	5020	1.6%
20328	02 Nov 2020	4700	0.6%
---etc.---
19400	19 Nov 2019	3692	1.3%
19376	01 Nov 2019	2133	0.0%
---etc.---
15010	14 Jan 2015	303	11.9%

So, the fills do not take place on a regular basis (like not every day).  I want to make a rolling 12-month look back for the average Waste Fraction, for instance all fills from 03 Nov 2019 to 02 Nov 2020 inclusive - on the same row as the end of the period.

I have got as far as:

AverageWaste = 
Var Year_Ago = DATEADD('Waste Fraction'[Filling Start Date],-1,YEAR)
Var Fill_Date = LASTDATE('Waste Fraction'[Filling Start Date])
Return CALCULATE(AVERAGE([Waste Fraction]),
            DATESINPERIOD('Waste Fraction'[Filling Start Date],Fill_Date,-1, YEAR)
        )

But all this seems to do is make the AverageWaste value the same as the Waste Fraction value on the row.  I have tried the same with BETWEENDATES as well as DATESINPERIOD but no joy.  I have also tried with a SUM instead of AVERAGE but get the same result.  I know that the Year_Ago and Fill_Date variables are working as I can return them to the row instead of the function.

There are loads of suggestions around, mainly similar to the above but none work for me 🙁

I don't have a date table in the model at the moment but it would be easy to do if one is needed. 

[lbendlin]

That is actually one of the advantages of having a Calendar table. All these little date hierarchies on date fields eat up a lot of memory for no real gain.  You can see the impact in DAX Studio.

[lbendlin · Accepted solution]

Ah, sorry, didn't notice you wanted a calculated column.  That doesn't really require a calendar table (but it is still a good practice in general).

AverageWaste = 
Var This_Date = [Filling Start Date]
RETURN AVERAGEX(FILTER('Waste Fraction',[Filling Start Date]<=This_Date && [Filling Start Date]>EDATE(This_Date,-12)),[Waste Fraction])

[ChemEnger]

Lovely job, thanks @lbendlin.  The only behaviour I wasn't expecting was that when I joined the Calendar table to the Data table, I lost the Date Hierarchy from the Filling Start Date field in the table, but no issue at all.

[lbendlin]

yes, a date/calendar table is sorely needed. Provide sample data if you like more help.

[ChemEnger]

Thanks @lbendlin,

So, I've added a date table (CALENDAR) and connected it to the Waste Fraction table.  Changed the code to:

AverageWaste = 
Var Year_Ago = DATEADD('Waste Fraction'[Filling Start Date],-1,YEAR)
Var Fill_Date = LASTDATE('Waste Fraction'[Filling Start Date])
Return CALCULATE(AVERAGE([Waste Fraction]),
 DATESINPERIOD('Calendar'[Date],Fill_Date,-365,DAY)
)

But still exactly the same result.

I have uploaded a copy of the raw data in Excel format to GitHub: https://github.com/ChemEnger/WasteFraction/raw/main/Waste%20Fraction.xlsx

pbix is on the same repository: https://github.com/ChemEnger/WasteFraction/blob/main/WasteFraction.pbix