Dynamic Forecast Comparison

[GBilger]

Hi,

The table below shows a summary of the data I'm working with.  Each row represents a different production forecast for a given year.  If I select a Year in the slicer, I want to display a waterfall chart to show the difference between production forecasts for that selected year starting with the first forecast and ending with the last (or Actuals for the current or past years). 

The standard waterfall chart works but it gives you subtotals in between each value that I don't want and I can't figure out how to make them go away.  I'm assuming to make it work I'll need a measure to find the incremental differences between forecasts.  If I'm wrong feel free to point me in the right direction.  I have a table where the forecasts are index and the volumes table isn't complicated (see below) but I'm struggling with the DAX.  

Product	Production Date	Volume	Forecast
Product1	1/1/2018	200	2018 PB
Product2	10/1/2018	300	2018 RBU1
Product3	1/10/2019	5000	2019 MTP
Product4	4/1/2021	675	Actual
Product5	6/1/2022	238	2020 LRP

[BeaBF · Accepted solution]

@GBilger what you can do is calculate a dynamic measure that returns the delta between the forecasts, so as to pass them directly into the chart and thus remove the subtotals, like this:

The graph above is the original one, in the one below I passed the DELTA measure as a value, calculated as follows:

DELTA = CALCULATE(SUM(Tabella[Volume]), ALLSELECTED(Tabella[Production Date ])) - CALCULATE(SUM(Tabella[PREV_VOLUME]), ALLSELECTED(Tabella[PREV_DATE]))

where PREV_VOLUME and PREV_DATE are two calculated columns:

PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)

PREV_DATE = LOOKUPVALUE(Tabella[Production Date ],Tabella[Indice],Tabella[Indice]-1)

instead Index is a column that you can insert from the power query in the sorting you prefer to give to the data to calculate the differential.

I hope I was clear, let me know,

B.

[BeaBF]

@GBilger If the solution is correct, I ask you to mark it, otherwise explain to me in more detail and let's see how to fix it!

B.

[BeaBF]

@GBilger Hi!

You can calculate an empty column like: Column= "" and put this new Column in the Breakdown field of the Waterfall Chart.

B.

[GBilger]

Hi,

The proposed solution did get rid of the labels for the increases/decreases.  However I was curious if there is a way to get rid of subtotal bars in between.  For this example it would just show the 2019 LRP and Actuals bars.  In between would be all the differences.