+2
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
Best answer by Nathan
View original
+11
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 !
+2
Hi! Thanks for the fast response
This Really helps to get the concept of the best practice!
Thank you Nathan!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.