How to dynamically compare two columns in a Waterfall chart?

[powerjey]

Hi everyone,

Hoping a Power BI expert can help me out with this problem. 

See attached for an example pbix file. https://drive.google.com/file/d/1QctBxMhENNiEE0XndtLQenXwsaif4zm5/view?usp=sharing

I need to dynamically compare the difference of two different forecasts with each other in a Waterfall chart.

Essentially, I would need the user to be able to select two different forecast dates and to be able to see immediately the difference in qtys between the two as well as with starting and ending totals.

I am currently using a custom visual (Simple Waterfall by Datanomy Limited) that allows for two different values to be inputted rather than native Power BI waterfall visual which only allows for the calculated variance column to be used.

The issue currently is that for a waterfall chart to work, a baseline value needs to be set so the difference can be calculated. I would like to be able to change the baseline values by allowing two different forecast dates to be compared.

One solution I was thinking of: Creating a custom dynamic measure in a matrix caluclating the variance of two selected columns and referencing that measure in the waterfall. But I'm not sure how to approach this as I'm not familar with DAX or if its possible. 

So the flow would be: 

• User selects two different forecasts
• Waterfall chart displays something like this:

My base data for reference:

Thanks everyone!

[jdbuchanan71 · Accepted solution]

@powerjey 

I got soemthing simple that is along the lines of what you are looking for I think.

First Forecast = 
VAR _fc = MIN ( Sheet1[Forecast] )
RETURN
CALCULATE( SUM ( Sheet1[Qty] ), Sheet1[Forecast] = _fc )

Second Forecast = 
VAR _fc = MAX ( Sheet1[Forecast] )
RETURN
CALCULATE( SUM ( Sheet1[Qty] ), Sheet1[Forecast] = _fc )

I also made a measure to use as the title of the chart.

Forecast Title = 
VAR _First = MIN ( Sheet1[Forecast] )
VAR _Second = MAX ( Sheet1[Forecast] )
RETURN _First & " to " & _Second

[powerjey]

Thank you so much! Very elegant and simple solution. I've learned something new today.

Appreciate it 🙂