6 Replies Latest reply: Dec 14, 2017 7:11 AM by Sunny Talwar

# Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

Hi Techies,

I am facing a problem while calculating growth% by Month-Year in Table chart. The result I am expecting is :

Month-Year  KPI Growth%

Jul-2017       A      10%(Val of Jul-2017 / Val of Jul-2016 -1)

Jul-2017       B      20%

Jul-2017       C      30%

Aug-2017     A      10%

Aug-2017     B      20%

Aug-2017     C      30%

Formula I am considerng is : July 2017 Sales/July 2016 -1

Please let me know how to do it

Best,

Rohit

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

Hi,

have you tried like below,

(Sum(Amount)-Above(Sum(Amount)))/Above(Sum(Amount))

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

How did you set the table as ?

KPI Month Year

A   Aug-2016

A   Aug-2017

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

Hi Devarasu,

Your solution is working execpt when I make a selection in Year , it is not giving mw growth%? Can you please tell me what to do for Year selection ?

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

Try this

(Sum(Amount)- Above(Sum({<Year, MonthYear>} Amount)))/Above(Sum({<Year, MonthYear>} Amount))

or simplify the above expression to this

Sum(Amount)/Above(Sum({<Year, MonthYear>} Amount)) - 1

But I still think that The As-Of Table is the right way to approach this...

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

Hi,

I've tackled this type of requests with the "As Of" approach in which I have a list of As Of periods, then a table that maps those As Of Periods with current and previous (rolling 3 or 12 months, financial vs calendar periods, etc.) and this table associated with your fact table. This approach makes calculations much simpler and it's more flexible, but you need to spend time building it. I guess the decision on the approach depends on the complexity of each App.

I posted this based on the As Of approach: My 2 cents on Time Analysis

Happy to discuss further,

I hope it helps,

Luis

PS. If it's something quick and simple, I wouldn't worry too much about it and follow Deva's approach

• ###### Re: Problem in calculating Growth by Month-Year(This year month vs Pre Year Month)

I agree with Luis here, that an The As-Of Table might be the best way to handle this situation.