Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
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
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
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
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?
Hey,
sure this makes sense ...
Wrap MAX(...) around the column that contains the month.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |