Set Analysis + previous month

[jakobjosef]

Hi dear Community,

does someone know how to show sales of the previous month?

In a text object, i would like to show how much sales increased compared to the month before.

So if my sales were like:

July: 100

August: 110

September: 150

Then my text object should show in September: 10 % (110/100 -1) (because sales increase from July to August for 10 %)

and in October: 36 % (150/110 -1)

Hope there is a simple solution, i could not manage to find a solution in different threads.

fyi: I know that "=Month(AddMonths(Today(),-1)) " Shows the last month, but how can I ad a  value?

Thank you very much for your help

Jakob

[timpoismans]

Adding to this, as Marcus suggested, in your Master Calendar table, I'd suggest you make a Month field, which contains the month as text, and a MonthNum field, which contains the month as number.

It's easier to compare to a number than text

If I may ask, why would you want to show the growth in a text object and not a table?

And how many months do you want to show?

What's your expected output?

[jakobjosef]

Ok good to know, thank you

And of course you can ask -

This text object is just for a very quick summary - to see the growth of sales in %, compared to the month before. A table would be too much Information - as everyone says - keep it short and simple.

My expected Output is a number in % which shows the growth-rate.

If there is a easier solution, please tell me

Greetings

Jakob

[timpoismans]

My expected Output is a number in % which shows the growth-rate.

So you want to show the growth rate based on the two previous months in the current month?

So today, you would show the growth rate from August vs July.

Next week, in October, you would show the growth rate from September vs August?

Just that one value?

[jakobjosef]

exactly!

[timpoismans]

And how would you handle the growth rate for a new year? Do you check the growth rate of December vs November in January?

[jakobjosef]

yes.

I already thought that this would cause technical challenges, but if it would not work for 1 month it wouldnt be a major problem.

[timpoismans]

This might be a bit too much for the purpose of it, but try the following.

vPreviousMonth = If(Month(Today())=1,12,Month(Today())-1)

vPrevious2Month = If(Month(Today())=1,11,If(Month(Today())=2,12,Month(Today())-2))

vCalcYear = If(Month(Today())=1,Year(Today())-1,Year(Today()))

vCalc2Year = If(Month(Today())=1 OR Month(Today())=2, Year(Today())-1,Year(Today()))

And then the measure:

Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

/

Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)

This would just continue comparing the two previous months over the years.

But to be really thorough, you'd have to do the following:

If(Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

    >= Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales),       

          (Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

          /

          Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)),

          -1+ (Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

          /

          Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)))

This would give you a positive percentage if your growth rate is >= 0 else it would give you a negative percentage, because you have dropped a certain percentage compared to the month before.