Forum Discussion
Solved
(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% ...
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])
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
