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 🙂

Accepted answers

All comments

rob_marshall

@RohanBiyani

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.

RohanBiyani

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?

rob_marshall

@RohanBiyani

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.

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…