'Excel 3 Month Rolling Average Pivot Table
I'm looking to find a way to calculate a 3 month rolling average in a pivot table but I'm running into some trouble.
I'm trying to average 'Count of Total Errors' for the past three months and use the date filter 'This Month' so that it's always up to date. I created a pivot table and added the data to the data model so I could create a measure. The formula I'm using is
=Calculate(AVERAGE([Count of Total Errors]/[Helper]),Filter(Range,Range[Date]<=EOMONTH(TODAY(),-1) && Range[Date]>=EOMONTH(TODAY(),-3)))
Where 'Total Errors' is what I'm trying to average and 'Helper' is a column of 1's to help with the average calculation (This is a trick I learned to use from another section in my excel workbook that worked for that particular instance and this is a similar situation)
However, when I try and use the above formula I get the error "This formula is invalid or incomplete: 'The AVERAGE function only accepts a column reference as an argument." When I remove AVERAGE from the formula I get "This formula is invalid or incomplete: 'Calculation error in measure 'Range'[3 Month Avg Total Errors]: The value for 'Helper' cannot be determined. Either 'Helper' doesn't exist, or there is no current row for a column named 'Helper'."
I'm not sure where to go from here.
Here's a sample of my data (the 'Helper' column is hidden):
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

