Month by Month yearly growth

[Anonymous]

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

[TomMartens · Accepted solution]

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

[TomMartens · Accepted solution]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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? 

[TomMartens]

Hey,

sure this makes sense ...

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

Regards,

Tom