How to construct a waterfall chat with difference year over year that works with the slicers

[c_tonetti]

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: 

Waterfall chart = GENERATESERIES(2011,YEAR(TODAY()),1)

and new columns:

Number of Pilots = CALCULATE(COUNT('Pilote File'[Sample number]),FILTER('Pilote File','Pilote File'[Year]='Waterfall chart'[Column]))

Difference1 = (CALCULATE(MAX('Waterfall chart'[Number of Pilots]),FILTER(ALL('Waterfall chart'),'Waterfall chart'[Year]=EARLIER('Waterfall chart'[Year])-1)) - 'Waterfall chart'[Number of Pilots])

The result is exactly what I need (see photo), except that it doesn't work with my filters. 

How can I do something like this that works with the slicers ?

Thanks in advance ! 

[v-rzhou-msft · Accepted solution]

Hi @c_tonetti 

You want to get a dynamic result in your water fall chart. However your Difference column is a calculated column.

Try to build measures to achieve your goal.

Number of Pilots = 
VAR _NumberofPilots = CALCULATE(COUNT('Pilote File'[Value]),FILTER(ALLSELECTED('Pilote File'),'Pilote File'[Year]= MAX('Waterfall chart'[Year])))
Return
_NumberofPilots

Difference = 
VAR _Difference = SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots]
Return
_Difference

Result in my sample.

Category slicer is built by category column in Pilote File(unrelated table)

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[c_tonetti]

Hi @v-rzhou-msft 

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:

Difference =

VAR _Difference = -(SUMX(FILTER(ALLSELECTED('Waterfall chart'),'Waterfall chart'[Year] = MAX('Waterfall chart'[Year])-1),[Number of Pilots])-[Number of Pilots])

Return

_Difference

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]

Best regards,

Camila 🙂