Forum Discussion
(12-month) Rolling Average Column
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.
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])
5 Replies
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])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.
yes, a date/calendar table is sorely needed. Provide sample data if you like more help.
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
