cancel
Showing results for
Did you mean:
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 ID Date Status Value WHAT HAPPENED? 1 01-01-2020 Open 100 Open 2 01-01-2020 Open 100 Open 3 01-01-2020 Open 100 Open 4 01-01-2020 Open 100 Open 5 01-01-2020 Open 100 Open 6 01-01-2020 Open 100 Open 1 02-01-2020 Open 100 Still Open 2 02-01-2020 Open 100 Still Open 3 02-01-2020 Won 100 Won 4 02-01-2020 Lost 100 Lost 5 02-01-2020 Open 80 Change 6 02-01-2020 Open 100 Still Open 7 02-01-2020 Open 100 New 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
Helper V

More like the beneath screenshot

Community Support

Hi @PeterStuhr ,

Is below what you need?

Check my .pbix file attached.

Best Regards,
Kelly

Community Support

Hi @PeterStuhr ,

Is below what you need?

Check my .pbix file attached.

Best Regards,
Kelly

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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
Top Kudoed Authors