cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Month by Month yearly growth

Hey Guys!

I've been looking around for a way to calculate cumulative sales growth having january first as a starting point.

I was first trying to work in a formula for the measure, but then I realized I could use the waterfall chart to have exactly what I was looking for.

My only problem is that when using the measure bellow, for january first, it calculates the growth rate from dec Y-1 to jan Y, and that messes up my year analysis.

The measure:

% M vs M-1 Sell Out =
IFERROR(CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]))/CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]);PREVIOUSMONTH(DimData[Mes Ano]))-1;BLANK())

and here is a picture of the graphich, explaining my problem. I need january to show as my zero, and from that point do the waterfall calculation.

Does anyone knows how to fix this, or another way to calculate the cumulative measure?

Thanks!!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

Re: Month by Month yearly growth

Hey,

the easiest way is to check if the current month contains the word "janeiro".

You can use the DAX function FIND like so

```IFERROR(
IF(FIND("janeiro";tablecolumnusedonaxis;;-1) = -1;
CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]))/CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]);PREVIOUSMONTH(DimData[Mes Ano]))-1
;0)
;BLANK()
)```

Maybe this is what you are looking for.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

Re: Month by Month yearly growth

Hey Tom!

Thanks for the idea!

I used MAX function as you sugested, and also I used FORMAT to transform my date to text and compare the word "janeiro".

This was the final formula, if anyone ever wants to check out:

```% M vs M-1 Sell Out 2 =
IFERROR(
IF(FIND("janeiro";FORMAT(MAX(DimData[Mes Ano]);"MMMM 'de' yyyy");;-1)=-1;
CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]))/CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]);PREVIOUSMONTH(DimData[Mes Ano]))-1;
0)

;BLANK()```

I loved the criativity behind your solution!

Thanks a lot!

My best regards,

Gabriel

4 REPLIES 4
Super User

Re: Month by Month yearly growth

Hey,

the easiest way is to check if the current month contains the word "janeiro".

You can use the DAX function FIND like so

```IFERROR(
IF(FIND("janeiro";tablecolumnusedonaxis;;-1) = -1;
CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]))/CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]);PREVIOUSMONTH(DimData[Mes Ano]))-1
;0)
;BLANK()
)```

Maybe this is what you are looking for.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

Re: Month by Month yearly growth

Hey Tom!

I liked the idea, but it did not work for me.

I don't know why, but the formula is not accepting the date column refference at "tablecolumnusedonaxis". Maybe it's because I am using a measure, and not a calculated column, but i'm not sure.

Do you know how to avoid it?

Super User

Re: Month by Month yearly growth

Hey,

sure this makes sense ...

Wrap MAX(...) around the column that contains the month.

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

Re: Month by Month yearly growth

Hey Tom!

Thanks for the idea!

I used MAX function as you sugested, and also I used FORMAT to transform my date to text and compare the word "janeiro".

This was the final formula, if anyone ever wants to check out:

```% M vs M-1 Sell Out 2 =
IFERROR(
IF(FIND("janeiro";FORMAT(MAX(DimData[Mes Ano]);"MMMM 'de' yyyy");;-1)=-1;
CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]))/CALCULATE(SUM('SELLIN E SELLOUT'[Sell Out]);PREVIOUSMONTH(DimData[Mes Ano]))-1;
0)

;BLANK()```

I loved the criativity behind your solution!

Thanks a lot!

My best regards,

Gabriel