I would like to write a formula where the intent is for the metric to calculate the average of the prior 3 months of the metric itself.
I envisioned the syntax being something like the following, though it says MOVINGAVERAGE doesn’t support recursive mode.
I understand that I could use MOVINGAVERAGE without PREVIOUS if the object being averaged was a separate metric, but the use case I have requires the formula to reference metric itself.
Is there any solution or workaround for this?
One way to calculate a moving average based on the prior months of the metric you are in is to use multiple PREVIOUS() functions with the same formula.
For instance, if you wanted each month to be the average of the previous 12 months you could write:
(previous(Month, 1) + previous(Month, 2) + … + previous(Month, 11) + previous(Month, 12)) / 12
I appreciate it isn’t the most elegant formula, but it should do the trick!