How to construct a waterfall chat with difference year over year that works with the slicers
Hi eveyrone ! I have a table called Pilot File and it has several columns (brand, zone, division, engineer, product family, metier) and rows (33k). Another table is used for the filters (brand, zone, division, engineer, product family, metier). I created a "clef" column in each table which concatenates all columns, so I was able to correlate both tables.
Now I want to construct a waterfall chart with difference year over year. I used this formula to create a new table:
First of all, thank you very much for your answer! It worked really well, except that the values had opposite signs, so I made a slight modification:
VAR _Difference = -(SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots])
However, I didn't understand it totally. I thought your formula would calculate only the difference between 2021 and 2020, since it uses the maximum year value (2021) and max year - 1 = 2020. Could you please explain me?
For other members, I also managed to resolve the problem in a different way that might be helpful:
Number of pilots = CALCULATE(COUNT('Pilote File'[Sample number]))
PARALELLPERIOD = CALCULATE([Number of pilots], PARALLELPERIOD('Calendar'[Date], -1, YEAR))
Difference = [Number of pilots] - [PARALELLPERIOD]