cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gabrielms123 Frequent Visitor
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.

image.png

 

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

 

 

Thanks!! 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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!
Gabrielms123 Frequent Visitor
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
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!
Gabrielms123 Frequent Visitor
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? 

 

image.png

Super User
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!
Gabrielms123 Frequent Visitor
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