Dynamic Waterfall Graph based on Start and End Date

[kjohnanand]

I'm trying to create a waterfall graph that tracks client wins/losses over a time period selected by a date slider. I'm using a switch/selected value combo function in order to achieve this but I can't get it to work properly.

Here's the switch function: 

Here's one of the measures that the switch function is bringing in (all are the same as this, just with different filters):

My data is unpivoted so date is in one column and active/inactive is in another column. 1 means the client is active and 0 means the client is not active. 

Here are my sort and category tables: 

Here's what I'm inputting into the waterfall boxes:

Here's the end result: 

What am I doing wrong here?

[mizan2390]

hi @kjohnanand 
The most probable reason your chart looks broken is purely a sorting issue.

A waterfall chart calculates left-to-right based on the visual order, so it is currently trying to subtract your losses before it even establishes the starting amount.

Here is how to fix it so it flows chronologically:

1. Sort the Category Column in the Data Model

• Go to the Table View (Data View) on the left sidebar of Power BI.

• Select your Waterfall Sort table.

• Click on the Category column header to highlight it.

• In the ribbon at the top (under the Column tools tab), click Sort by column and select your Order column. This forces Power BI to sort the text based on your 1, 2, 3 numbering.

2. Update the Visual Sort Settings

• Go back to your report canvas and select the waterfall chart.

• Click the More options (...) icon in the top right corner of the visual.

• Hover over Sort axis and select Category.

• Click the (...) icon one more time, hover over Sort axis, and ensure Sort ascending is checked.

Once you do this, your X-axis will snap into the correct order: Starting Amount -> Wins -> Losses -> Total.

Your DAX for the Waterfall Value is perfect. Because you multiplied [Waterfall Losses] by -1, it will correctly drop down as a red bar. The native "Total" bar at the end will then automatically calculate your Ending Amount (Starting Amount + Wins - Losses).

Give that a try.

If this solves your problem, give it a kudo and mark this as a solution.
@me so that i don't loose your thread.