Solved

CAGR Function

  • 10 February 2022
  • 2 replies
  • 121 views

Userlevel 2
Badge

Hello,

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?

Roy,
Melio

icon

Best answer by Nathan 10 February 2022, 12:54

View original

2 replies

Userlevel 4
Badge +4

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

Hi! Thanks for the fast response

 

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

 

Thank you Nathan!

Reply