cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterStuhr
Helper V
Helper V

Create waterfall based on changes between dates

Hi all,

 

We need help creating a waterfall of our Pipeline. Based on 2 selected dates, we would like to see what happened to our deals. Which did we win, which did we lose, which changes values and which new deals have been added.

 

If we take the following data as example:

 

Deal IDDateStatusValueWHAT HAPPENED?
101-01-2020Open100Open
201-01-2020Open100Open
301-01-2020Open100Open
401-01-2020Open100Open
501-01-2020Open100Open
601-01-2020Open100Open
102-01-2020Open100Still Open
202-01-2020Open100Still Open
302-01-2020Won100Won
402-01-2020Lost100Lost
502-01-2020Open80Change
602-01-2020Open100Still Open
702-01-2020Open100New Deal - Open

 

Let's compare 01-01-2020 to 02-01-2020.

 

The first pillar of the Waterfall should always be "Value of OPEN deals" of the "starting date".

For the above example, we have 6 deals Open on 01-01-2020 worth a total of 600 in Value.

 

The Last pillar (ending point) of the Waterfall should always be the "Value of OPEN deals" of the "Ending Date".

For the above example, we have 5 deals Open on 02-01-2020 worth a total of 480 in Value.

 

The Values in between the starting end end dates has to be the following:

 

WON: Sum of deals that was Open at starting date, but is now Won at ending date. (also New deals added in between, that is already Won too). Should be negative number.

LOST: Sum of deals that was Open at starting date, but is now Lost at ending date. (also New deals added in between, that is already Lost too). Should be negative number.

CHANGE: Sum of "the change" of deals that was Open at starting date, and still Open at ending date. 

NEW: Sum of deals that was not existing at starting date, but is now existing.

 

Using the above example:

 

Starting Date:

 

STARTING DATE OPEN: 600

 

WON: -100

LOST: -100

CHANGE: -20

NEW: 100

 

ENDING DATE OPEN: 480

 

 

I hope this is possible!

 

On top of this, we would like to be able to "Click" on for instance the "Won" pillar, and see which deals this affects!

 

Thanks!!

 

3 REPLIES 3
PeterStuhr
Helper V
Helper V

Hi @v-kelly-msft 

 

More like the beneath screenshot

 

testdata.JPG

 

Hi @PeterStuhr ,

 

Is below what you need?

v-kelly-msft_0-1621934004752.png

Check my .pbix file attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

v-kelly-msft
Community Support
Community Support

Hi @PeterStuhr ,

 

Is below what you need?

v-kelly-msft_1-1621588105147.png

 

v-kelly-msft_2-1621588134605.png

 

Check my .pbix file attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors