How to calculate Month to month Growth Rate?

JoannaAn
Contributor

How to calculate Month to month Growth Rate?

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
JaredDolich
Moderator

Re: How to calculate Month to month Growth Rate?

@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!

 


Jared Dolich

View solution in original post

Akhtar.shahbaz
Community Boss

Re: How to calculate Month to month Growth Rate?

Hi @JoannaAn ,

 

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

Screenshot_47.png

Screenshot_48.png

 

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

 

Thanks

Akhtar

View solution in original post

12 REPLIES 12
JaredDolich
Moderator

Re: How to calculate Month to month Growth Rate?

@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!

 


Jared Dolich

View solution in original post

JoannaAn
Contributor

Re: How to calculate Month to month Growth Rate?

@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...

ChrisHeathcote
Community Boss

Re: How to calculate Month to month Growth Rate?

Makes sure the summary method is formula not sum.
Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
JoannaAn
Contributor

Re: How to calculate Month to month Growth Rate?

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...

ChrisHeathcote
Community Boss

Re: How to calculate Month to month Growth Rate?

Ok

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.

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
JoannaAn
Contributor

Re: How to calculate Month to month Growth Rate?

Ok! Thank you for your suggestion.

I will try to do so.

 

 

abhay.kanik
Regular Contributor

Re: How to calculate Month to month Growth Rate?

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

JoannaAn
Contributor

Re: How to calculate Month to month Growth Rate?

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)

JaredDolich
Moderator

Re: How to calculate Month to month Growth Rate?

@JoannaAn 

I think you're very close. @ChrisHeathcote 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!


Jared Dolich