Forum Discussion

ChemEnger's avatar
ChemEnger
Advocate V
5 years ago
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% ...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    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])
lbendlin's avatar
lbendlin
Super User
5 years ago

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's avatar
    ChemEnger
    Advocate V
    5 years ago

    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.