Year-on-year growth rate
I have a line item 'Revenue' with monthly data. I'm trying to calculate the year on year growth rate for this line item. Currently, I tried the formula below:
Revenue / OFFSET (Revenue, -12, 0) - 1
This formula is working fine for all the months, but it is not working for Quarters or Half or Full Financial Years (FYs). The summary method is currently none and when I try to change the main summary or time summary to formula, it does not work and causes an error. With the current summary method as none, there are blanks instead of growth rate values for the quarters, HYs , FYs. I want to formulate this growth rate such that it always takes the value from 12 months ago whilst calculating. (For eg- if time period is Q1 FY24, it will take the value of Q1 FY23 for growth rate calculation)
And I want to do the above in just one line item without having to create a new helper line item as I need to calculate this growth rate in a similar way for many line items. Any help please? thank you in advance 🙂
Answers
-
I believe your issue is when you get to Quarters, Half, and Full Years, it is still looking 12 periods back. So, at Quarter 1, it is looking 12 quarters prior, when looking at half years, it is looking at 12 half years back. A better way to doing this is making the -12 (in your formula) dynamic so at months, it is -12, at quarters it is -4 and half it -2 and at year it is -1.
0 -
Thank you for the response. I tried to implement this but am unable to manually input values for quarters or full years in a helper line item. Can you please advice me how I can practically implement the above solution?
0 -
Instead of doing what I was said earlier, just change the summary method to SUM as that will sum the months and should be correct.
0
