CAGR Function

  • 10 February 2022
  • 2 replies

Userlevel 2
Badge +2


Is there a formula that outputs cumulative average growth rates of a certain set of numbers? For example, an output of the last three months' revenues average growth rate could help model the rolling forecast (getting the trend is sometimes better than getting just the average).

Does that make sense?



Best answer by Nathan 10 February 2022, 12:54

View original

2 replies

Userlevel 5
Badge +6

Hi Roy,


we don’t have a function that can do this precisely, but you can build this with several metrics and the MOVINGAVERAGE() function



3 Months Average formula is simply =

​​​​​​​ MOVINGAVERAGE(Revenue,3)

3 Months Growth =

 if(isblank(Revenue),blank,('3 Months Average'-Revenue[Select:Month-3])/Revenue[Select:Month-3])

Simply comparing the Average vs the revenue of 3 months back; but adding a check to not do it when we don’t have revenue yet

Forecast = 

if(isdefined(Revenue),Revenue,previous(Month)*(1+'3 Months Growth'[remove lastnonblank:Month]))

Translation: IF(there is actual revenue, we take revenue, previous value * (1+ last calculated growth rate)


Depending on your current app, you probably have a better way of doing the switchover (the check if the period is in the past vs future where we forecast).


I hope this helps !


Userlevel 2
Badge +2

Hi! Thanks for the fast response


This Really helps to get the concept of the best practice!


Thank you Nathan!