Skip to main content
Solved

Combine MOVINGAVERAGE and PREVIOUS functions for run rate planning

  • February 1, 2023
  • 1 reply
  • 426 views
J
Seasoned Pigmenteer
Forum|alt.badge.img+5

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.

MOVINGAVERAGE(PREVIOUS(month),3)

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?  

Best answer by Matt D

Hi Justin,

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!

Thanks,
Matt

View original
Did this topic help you find an answer to your question?

Matt D
Employee
Forum|alt.badge.img+6
  • Matt DEmployee
  • Employee
  • February 6, 2023

Hi Justin,

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!

Thanks,
Matt

Matt

Reply


Ask your question here!

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings