How to calculate Month to month Growth Rate?

JoannaAn

Hi all!

What is the way to calculate Month to Month Growth rate (from the beginning of the time scale)

if I have Sales Volume by month at monthly timescale?

I have:

(Volume by Month - PREVIOUS (Volume by Month)) /PREVIOUS (Volume by Month)* 100%

I wrote the formula but it doesn't match the math result (when I'm making calculation check)

Thank you.

Accepted answers

JaredDolich

@JoannaAn

I like this formula but you might consider calculating the PREVIOUS(Volume) once as a line item then reference it. Also, you can just multiply by 100 (you can leave the percentage off). Lastly, the PREVIOUS function won't work too well on the first month since there's no previous for it to refer to. If the first month is important you can set up a separate module that contains just the last month of the prior year. You'll then need to use a Boolean to identify the first month so you know to refer to the last month of the prior year.

Hope this helps!

Akhtar.shahbaz

Hi @JoannaAn ,

Based on your table, i have done the below calculation.

Add the logic if you want last month volume to be populated in the first months @JaredDolich mentioned

Thanks

Akhtar

All comments

JaredDolich

@JoannaAn

Hope this helps!

abhay.kanik

Hi,

Formula looks correct, also you can optimize by the approach @JaredDolich suggested. Can you share a couple of instance where it is giving wrong output.

Thanks

Abhay

ChrisAHeathcote

@JoannaAn

Break the formula out over several line items.

Volumes

Prior Volumes

Growth

You can also use an IF THEN ELSE statement to populate month 1 for Prior Volumes

=IF PREVIOUS(Volumes) <> 0 THEN PREVIOUS(Volumes) ELSE Volumes

This makes the assumption that this is the first month so closing volumes equal closing volumes. It Also assumes that every other month with have volumes greater than zero. If this is not the case then use @JaredDolich method of populating month 1.

Also, remove the * 100% and simply format the line item as a % in the blueprint.

JoannaAn

@JaredDolich

Thank you for the quick reply.

I just not sure that I'm using functions correctly.

And that's my formula is correct...

Because I'm getting different numbers by regular calculator..

So..what I would like to do is:

Growth (Feb20) = (34, 463 (Feb20) - 19,691(Jan20)) / 19,691(Jan20) * 100% = 75, 019 %

Growth (Mar20) = (39, 270 - 34, 463) / 34, 463 * 100% = 13, 94 %

Screenshot 2021-05-11 at 17.07.26.png

And here I have those huge numbers in the last row...

ChrisAHeathcote

Makes sure the summary method is formula not sum.

JoannaAn

Hi!

Thank you for your reply.

Yes, summary method was Sum..but when I'm trying to change it, it says:

PREVIOUS function cannot be used for summary time periods...

JoannaAn

Hi! Thank you for the reply.

I just posted my table with some numbers...

And more particular explanation if what I'm trying to do.. (Im thinking that may be I'm using wrong functions? not sure)

ChrisAHeathcote

You will need to use the ratio summary method.

Create two line items that calculate each side of the ratio and use these to populate the ratio settings.

The two new line items should both be set to sum.

JaredDolich

@JoannaAn

I think you're very close. @ChrisAHeathcote suggestion to turn off the SUM in summary method is a good one. Use Ratio or formula instead. I believe the reason you're seeing large percentages is because the values are being summed up the hierarchy. You got this! Good job on the formula!

JoannaAn

Ok! Thank you for your suggestion.

I will try to do so.

Akhtar.shahbaz

Hi @JoannaAn ,

Based on your table, i have done the below calculation.

Add the logic if you want last month volume to be populated in the first months @JaredDolich mentioned

Thanks

Akhtar

JoannaAn

Oh! Thank you @Akhtar.shahbaz!

For now I only tried ratio, and Ratio didn't work for me for some reason...

And to make the Line item for the Previous months worked for me well.

(and it also allowed me to change sum - to formula that I couldn't do before)

I see now what @JaredDolich meant ...

Thanks to you,

and also to @JaredDolich and @ChrisAHeathcote

I very much appreciate community help! 😃

Joanna.

Quick Links

Can you help?

Unanswered questions

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…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…