To calculate a rolling average using the last 12 months data up to date, you can use Metrics as tool to calculate the value:
Formula Theory
(Total of previous 12 months - this month last year + this month) / 12 = Rolling Average
Create the following Metrics using Add a New Metric
Previous 12 months
Remove this month last year
Add This Month
Rolling Average 12 months
To commence the calculation
Create an amount to reference in the first period of your Budget or Actuals
Manually sum the previous 12 months value
Add the total amount to Previous 12 Months metric line
Record the amount in the month preceding your calculation start period
Leave the other metric lines blank
Add Formulas to the Metrics for both Budget and Actual values
Previous 12 Months: Period -1
Sum of values on left side only: Previous 12 months; Remove this month last year; Add This Month
Remove this month last year: Period -12
Accounts / Metrics for calculation X -1 (to create a negative value)
Add This Month: Period 0
Accounts / Metrics for calculation
Rolling Average 12 months: Period 0
Previous 12 Months; Remove this month last year; Add This Month / 12
This example is the Rolling 12 month Average above:
