How to build a Pipeline Waterfall from scratch with Power BI

The pipeline waterfall report is one of the advanced visuals the sales department can benefit from. Sales Operations often get access to this analysis through third-party forecasting software companies. However, it’s not uncommon to have data issues with this visual. In another article, I discuss issues you can have with Salesforce Einstein Analytics built-in pipeline waterfall reports. Issues could be due to Salesforce settings or data, or the third-party software itself.

The best way to make sure that what you’re buying works is to master the report logic. For this reason, I took the time to build this visual on my own using Power BI. If you’re using Salesforce, you’ll be able to leverage this report, as the metadata I’m using is standard in any Salesforce organization.

If you’re a BI developer, I have no doubt that you’ll be able to integrate this to your enterprise model. There are many benefits to this, as relying on third parties limits the possibilities. Namely, you can add this visual in existing reports, you can change currencies (which is not always possible with third parties), you can use any columns available in your data model to slice and dice the visual, you could also add new waterfall categories relevant to your business, etc.

What’s a Pipeline Waterfall Chart?

The purpose of this chart is to explain why we started with an open pipeline of X USD at the beginning of a period, and ended up with Y USD at the end.

Therefore, there is usually a timeframe that corresponds to the period you wish to analyze. I called it “Pipeline Dates” for lack of a better name. In this screenshot below, we’re looking to understand how the open pipeline evolved during Q1FY20. There’s also another timeframe, which corresponds to the opportunities close dates. Here, I took the whole FY20 fiscal year.

Notice that there are about 10 categories on the chart below. These categories help you understand how and why your pipeline changed during a certain timeframe.

With this visual, for a Sales Rep, we analyze his pipeline movement during Q1FY20, for opportunities that close in FY20. We can see that he:

• started the quarter with a pipeline of 375 K€
• created 180 K€ of pipeline during the quarter
• increased existing opportunity amounts for 14 K€
• moved in 28 K€ from opportunities closing outside FY20 to FY20
• reduced existing opportunity amounts for 31 K€
• won 133 K€, from which 92 K€ was created during the quarter
• lost 96 K€
• ended up with a pipeline of 335 K€ for FY20 at the end of the quarter

What’s the data needed to build the report?

Now that we know why the pipeline waterfall can bring insights to the sales department, we’ll explore the data needed to build this report.

First, you obviously need to import Opportunities. The following SOQL query is sufficient to build the report. Notice that I only imported standard field, so this query will work for your company. However, you could import all custom fields relevant for you.

SELECT Amount,CloseDate,CreatedDate,CurrencyIsoCode,ForecastCategory,Id,IsClosed,IsWon,Name,OwnerId,StageName
FROM Opportunity

Secondly, we need to import the standard object Opportunity History. This object tracks some changes made to fields such as Amount, CloseDate and StageName. This is the key table to make this work. Again, I only used standard fields.

SELECT Amount,CloseDate,CreatedDate,CurrencyIsoCode,ForecastCategory,Id,OpportunityId,StageName
FROM OpportunityHistory

You’ll also need the CurrencyType table if you’re using multiple currencies in your organization. This will allow you to see the pipeline waterfall in any currency.

SELECT ConversionRate,DecimalPlaces,Id,IsActive,IsCorporate,IsoCode 
FROM CurrencyType

Lastly, you’ll need the standard object OpportunityStage. This table stores all stages, even inactive ones. It will tell you whether a stage is closed or open, and is won or lost.  

SELECT ApiName,ForecastCategory,ForecastCategoryName,Id,IsActive,IsClosed,IsWon,MasterLabel 
FROM OpportunityStage

Putting this all together, we have the following model:

The model is straightforward. You need to pull at least 4 Salesforce objects:

• Opportunities
• Opportunity History
• Opportunity Stages
• Currencies

You could add a dimension for end users if you wanted to have additional ways to look at the chart.

All Salesforce organizations have the objects mentioned above. The Opportunity History table tracks changes made to the Opportunity object. That’s the key object for this report. For example, if a SFDC user changes an opportunity stage from “Prospecting” to “Closed Won”, Salesforce is going to create a record in this Opportunity History table. By default, I think a few fields are marked to be tracked:

• Amount
• Stage
• Close Date

You need to track at least these 3 fields to make this work.

Notice I added 2 date tables to allow the users to change the pipeline dates and close dates.

Lastly, the Pipeline Waterfall table is a DAX table for the Pipeline Waterfall visual. We’ll explain this one later in the chapter: “Putting it all together in a chart”.

Technical solution

General concept

I’m sure there are many ways to tackle this project. I chose to compare the state of each Opportunity record at the beginning of the period, with its state at the end of the period. The “state” is materialized by an opportunity history record, as only one opportunity history record can be valid at a certain point in time. In the end, I chose to compare the Opportunity History record valid at the end of the pipeline dates, with the opportunity history record valid at the beginning.

Let’s take an example. I took “Acanthaster planci Cardify Optional” Opportunity record.

This opportunity has 4 opportunity history records.

What’s relevant for us are the 3 last lines, as the user changed the amount in line 3 from 50 in line 2 to 0, and he changed the stage from Needs Analysis in line 3 to Closed Lost in line 4.

In our example, Q1FY20 correspond to 4/1/2019 to 6/31/2019. If we were to do the waterfall manually, how would we process this opportunity?

We’d first ask ourselves: what was the state of this opportunity at the beginning of the period, the 1^st April 2019, and what was its state at the end, the 31^st June 2019?

The 1^st April 2019, line 2 was still valid, as the opportunity record was created before the 1^st April 2019 and the next opportunity was created after the 1^st April 2019. Therefore, the opportunity was in the stage “Needs Analysis”, which is open, for 50.66€. The close date is valid, as it’s in FY20.

The 31^st June 2019, the opportunity was also in the state of line 2, meaning it was still open for 50.66€. Therefore, in the waterfall chart, this opportunity would count for 50.66€ in Pipeline Start, and 50.66€ in Pipeline End.

Let’s take a more complex example before jumping to any conclusion. Let’s take: Acinynox jubatus Matsoft Multi-tiered

The 1^st April 2019, line 2 was valid. The opportunity was Open for 2,925.14€.

The 31^st June 2019, line 6 was valid. The opportunity was Open for 1,334.73€.

Therefore, in the waterfall chart, this opportunity would count for 2,925.14€ in Pipeline Start, and 1,334.73€ in Pipeline End. Additionally, we’d have to put 1,590.41€ in the Reduce category, as the opportunity amount was reduced.

Notice that the opportunity increased in line 3, then reduced in line 4, and then reduced drastically in line 6. We don’t want to track all changes that happened during the period, as it doesn’t provide additional insights and cause balance issues. The opportunity has been reduced for 1,590.41€ during the period, end of story.

Let me pause here to explain this part thoroughly.

The example above is still quite simple, but an opportunity can have craziness hidden in its related opportunity history records. For example, nothing prevents an opportunity to be open, then closed won, then reopen, then closed won again, and so on, 20 times. If it was the case for a 10K€ opportunity, and if you decided to track ALL changes, you’d have:

• Start: 10 K€
• Reopen: 190 K€
• Closed Won: 200 K€

This analysis would be wrong, as we simply closed a 10K€-deal. The proper logic would be the following:

• Start: 10 K€
• Closed Won: 10 K€

Ok, we learned a lot. We now understand that we need to compare the state of each Opportunity record at the beginning of the period, with its state at the end of the period. We also understood that the movements should be balanced.

Now let’s see how we do this with Power BI.

Understand Opportunity History records validity

The first thing I did was to flag opportunity history records validity to identify easily the opportunity history record valid at the beginning of the period and the one valid at the end of the period. I created 2 calculated columns on the Opportunity History table:

• Valid From Date
• Valid To Date

The opportunity history record is valid from the date of its creation. That’s why we have:

Valid From Date = 'Opportunity History'[CreatedDate]

The opportunity history record is valid until the next opportunity history record. The trick here was to create an additional column to make this possible, a sort key. Indeed, even if that’s rare, 2 opportunity history records could be created at the same time, which causes issues if we try to locate the next opportunity history record solely based on its created date. The calculated column called “Opportunity History Sort Key” below solves this issue. You can use SFDC IDs, as they sort well according to their created date.

Opportunity History Sort Key = FORMAT('Opportunity History'[CreatedDate],"yyyyMMddhhmmss")&'Opportunity History'[Id]

We then have this Valid To Date column, which returns the created date (or the valid from date), of the next opportunity history record.

Valid To Date = 
VAR SelectedOpportunity = 'Opportunity History'[OpportunityId]
VAR SelectedCreatedDate = 'Opportunity History'[CreatedDate]
VAR SelectedKey = 'Opportunity History'[Opportunity History Sort Key]
VAR ValidToDate = 
    CALCULATE(
        MIN('Opportunity History'[CreatedDate]),
        FILTER(
            ALL('Opportunity History'),
            'Opportunity History'[OpportunityId]=SelectedOpportunity && 
            'Opportunity History'[Opportunity History Sort Key]>SelectedKey
        )
    )
RETURN ValidToDate

Here are the fields in action:

It’s a good start, but it’s not enough to identify easily all opportunity history records valid at start, and all valid at end. To get this, I created the following flags:

• CloseDate is valid during period
• Pipe dates are valid at start
• Pipe dates are valid at end
• Record is valid at start
• Record is valid at end

Indeed, an opportunity history record is fully valid at start if its close date is valid during the close date timeframe selected, and the pipeline dates are valid at the start of the pipeline period selected by the user.

First, I created 4 measures to get the exact dates of the two timeframes selected by the user:

Close Date End = MAX('Close Date'[Date])
Close Date Start = MIN('Close Date'[Date])
Pipeline End = MAX('Pipeline Date'[Date])
Pipeline Start = MIN('Pipeline  Date'[Date])

CloseDate is Valid during period = 
VAR SelectedCloseDate = SELECTEDVALUE('Opportunity History'[CloseDate])
VAR Result = 
    IF(
        SelectedCloseDate>=[Close Date Start] &&
        SelectedCloseDate<=[Close Date End],
        1,
        0
    )
RETURN Result

This measure above returns 1 if the close date is valid. All my flags are 0s and 1s.

Pipe Dates are Valid at start = 
    VAR ValidFromDate = SELECTEDVALUE('Opportunity History'[Valid From Date])
    VAR ValidToDate = SELECTEDVALUE('Opportunity History'[Valid To Date])
    VAR Result = 
        IF(
            AND(
                ValidFromDate<[Pipeline Start],
                OR(
                    ISBLANK(ValidToDate),
                    ValidToDate>[Pipeline Start]
                )
            ),
            1,
            0
        )
    RETURN Result

We don’t forget to say that if the Valid To Date field is blank, it means that the date is still valid. That’s why we have ISBLANK(ValidToDate) in the code.

Pipe Dates are Valid at end = 
    VAR ValidFromDate = SELECTEDVALUE('Opportunity History'[Valid From Date])
    VAR ValidToDate = SELECTEDVALUE('Opportunity History'[Valid To Date])
    VAR Result = 
        IF(
            AND(
                ValidFromDate<[Pipeline End],
                OR(
                    ISBLANK(ValidToDate),
                    ValidToDate>[Pipeline End]
                )
            ),
            1,
            0
        )
    RETURN Result

Record is Valid at start = 
    IF(
       [Pipe Dates are Valid at start]=1 && 
       [CloseDate is Valid during period]=1,
        1,
        0
    )

Simply, if the pipeline dates are valid at start and the close date is valid, then the opportunity history record is valid at start.

Record is Valid at end = 
    IF(
       [Pipe Dates are Valid at end]=1 && 
       [CloseDate is Valid during period]=1,
    1,
    0
)

We can see these flags in action in the example we took previously on the last 2 columns:

We can see that the second line is the only record valid at start, and the line 6 is the only one valid at end. It works as expected.

Computing Pipeline Start and Pipeline End

From this point, we’re already ready to compute the first and last waterfall measures:

• Pipeline Start
• Pipeline End

Indeed, the amount of open pipeline at the beginning of the period is the sum of all open, valid at start opportunity history records.

The amount of open pipeline at the end of the period is the sum of all open, valid at end opportunity history records.

I created 2 measures to identify these records:

00. Pipeline Start = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at start]=1 && 
            'Opportunity History'[Stage IsClosed]=FALSE()
        )
    )

11. Pipeline End = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            'Opportunity History'[Stage IsClosed]=FALSE()
        )
    )

Great! We already have the logic for 2 categories!

We’ll see how to leverage these flags later and how to show the results in the chart. For the moment, we’ll keep building these flags that correspond to all waterfall categories.

We can see the flags in action on the screenshot below (last 2 columns), using the same example.

Thinking about possible categories

Now we have the 2 most obvious categories, the pipeline start and the pipeline end values. What about other categories? How to make sure we have counted all categories? How many are there?

The key is to remember the fields we’re interested in:

• Amount
• Stage
• CloseDate

We’ll start with this. First, the amount of an opportunity can increase or decrease: that’s 2 additional categories. We’ll call these “Expand” and “Reduce”.

Looking at the stage now, we’re generally interested in Open, Closed Won and Closed Lost. Opportunities that changed from Open to Closed Won and Closed Lost are interesting, so that’s 2 additional Categories. But also, an opportunity can be Closed Lost at the beginning of the period, and then Open at the end. That’s probably rare, but we have to take these into account. We’ll call these: “Reopen Opportunities”.

Regarding Close Dates, an opportunity’s close date could be outside the close date timeframe we’re looking at the beginning of the period, but inside the same timeframe at the end. We’ll call these “Moved In Opportunities”. The opposite scenario is possible, and we’ll call these “Moved Out Opportunities”.

We’re missing one important scenario: opportunities created during the period.

 Let’s sum this up:

1. Pipeline Start
2. Expand
3. Reduce
4. Closed Won
5. Closed Lost
6. Reopen
7. Moved In
8. Moved Out
9. Pipeline End
10. New Opportunities

Let’s organize these categories a bit based on whether it increases the open pipeline or it decreases it.

• Pipeline Start
• Movements that increase the pipeline
  • New
  • Reopen
  • Expand
  • Moved In
• Movements that reduce the pipeline
  • Reduce
  • Moved Out
  • Closed Won
  • Closed Lost
• Pipeline End

Now, depending on which fields you’re tracking and what you’re interested in, you could add any category relevant for you. For example, you could be interested in splitting the Closed Won categories into 2 categories:

• Closed Won New
• Closed Won Old

Closed Won New being closed won opportunities created during the pipeline period, and closed won old the other closed won opportunities. This shows how much sales representatives rely on existing pipeline to make their quota.

As a sales representative, you could be interested in opportunities reassigned to other sales representatives. By tracking the Opportunity Owner field and by creating the right flag, you would be able to do so.

That’s also the power of doing this on your own: you can create the flags relevant for your business without having to rely on a 3^rd-party product roadmap.

Let’s say that you want to split the Moved In category into 2 categories as well:

• Moved In from past
• Moved In from future

This will allow you to understand if your representatives can close deals faster than expected, or if they have an issue cleaning their pipeline and pace their deals. Indeed, moved in could be a good thing as well as a bad thing. The category on its own doesn’t tell you the whole story.

In the end, here’s the target in the tool I’m building:

1. Pipeline Start
2. New
3. Reopen
4. Expand
5. Moved In
6. Reduce
7. Moved Out
8. Won and new
9. Won not new
10. Lost
11. Pipeline End

Computing all categories

New

A new opportunity has been created during the period.

01. New = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            [Opportunity Is Created During Period]=1
            
        )
    )

Here’s the “Opportunity Is Created During Period” flag I used:

Opportunity Is Created During Period = 
    VAR OpportunityCreatedDate = SELECTEDVALUE('Opportunity History'[Opportunity Created Date])
    VAR Result = 
        IF(
            AND(
                OpportunityCreatedDate>=[Pipeline Start],
                OpportunityCreatedDate<=[Pipeline End]
            ),
            1,
            0
        )
    RETURN Result

Won (New and Not-New)

For a record to be won, the record must be valid at end, and the stage should be won, thus we have the highlighted lines:

08. Won = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            'Opportunity History'[Stage IsWon]=TRUE
        )
    )

It’s interesting to note that with this, we have small discrepancies. For example, you could have an opportunity won at the end of period, which was already won at the start of period. In my file, that’s the case for the Opportunity ID = 156969. Let’s look at this opportunity if we removed line 7.

What do we do with this opportunity? Should we count it ? I think we should, as the opportunity is, after all, won in Q1FY20.

If we count it as won, we definitely would have to balance it with a category on the “Increase” side. Let’s look at these categories :

1. Pipeline Start
2. New
3. Reopen
4. Expand
5. Moved In

I’ll put this in the “Pipeline Start” category. Thus, we have to tweak the Pipeline Start measure a bit. We can’t exclude all closed opportunities. We’ll keep closed opportunities valid at end.

00. Pipeline Start = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            ([Record is Valid at start]= 1 && 'Opportunity History'[Stage IsClosed] = FALSE()) ||
            ([Stage IsClosed at Start] = TRUE() && [Record is Valid at end]  = 1 && 'Opportunity History'[Stage IsClosed] = TRUE()) && 
            [Has an open record Valid During Period]=0
        )
    )

From there, it’s easy to get “Won Not New” and “Won New”

08. Won Not New = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            'Opportunity History'[Stage IsWon]=TRUE &&
            [Opportunity Is Created During Period]=0
        )
    )

09. Won and New = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            'Opportunity History'[Stage IsWon]=TRUE &&
            [Opportunity Is Created During Period]=1
        )
    )

Lost

The Lost flag is also like the won flags. I just added a condition: the stage must be lost

10. Lost = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            'Opportunity History'[Stage IsLost]=TRUE
        )
    )

Reopen

For an opportunity to be reopen, it’s stage at start had to be closed. Thus, I created a measure called “Stage IsClosed at Start”, which must be true.

This measure return the column “Stage IsClosed” for the opportunity history record valid at start.

Stage IsClosed at Start = 
VAR SelectedOpportunity = SELECTEDVALUE('Opportunity History'[OpportunityId])
VAR Result =
    CALCULATE(
        SELECTEDVALUE('Opportunity History'[Stage IsClosed]),
        FILTER(
            FILTER(
                ALL('Opportunity History'),
                'Opportunity History'[OpportunityId]=SelectedOpportunity
            ),
            [Record is Valid at start]=1 
        )
    )
RETURN Result

Therefore, the formula is the following one:

02. Reopen = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            ('Opportunity History'[Stage IsClosed]=FALSE() || [Has an open record Valid During Period]=1 || [CloseDate at Start is Valid during period] = 0) &&
            [Stage IsClosed at Start] = TRUE
        )
    )

Expand

For an opportunity history record to fall into the Expand category, several things need to happen. First, it’s amount at the end must be greater than it’s amount at start.

Thus, the first measure we’re going to need is the “Amount at Start”, that returns the Amount of the opportunity history record valid at start.

Amount at Start = 
VAR SelectedOpportunity = SELECTEDVALUE('Opportunity History'[OpportunityId])
VAR Result =
    CALCULATE(
        [Amount],
        FILTER(
            FILTER(
                ALL('Opportunity History'),
                'Opportunity History'[OpportunityId]=SelectedOpportunity
            ),
            [Record is Valid at start]=1
        )
    )
RETURN Result

Therefore, the opportunity must have an open record valid at start.

Has an open record Valid At Start = 
VAR SelectedOpportunity = SELECTEDVALUE('Opportunity History'[OpportunityId])
VAR Result =
    IF(
        COUNTROWS(
            FILTER(
                FILTER(
                    ALL('Opportunity History'),
                    'Opportunity History'[OpportunityId]=SelectedOpportunity
                ),
                [Record is Valid at start]=1 && 'Opportunity History'[Stage IsClosed] = FALSE()
            )
        )>0,1,0
    )
RETURN Result

03. Expand = 
    SUMX(
        FILTER(
            'Opportunity History',
            [Amount]>[Amount at Start] &&
            [Pipe Dates are Valid at end]=1 &&
            [Has an open record Valid At Start] = 1
        ),
        [Amount]-[Amount at Start]
    )

Reduce

The reduce flag is like the expand flag, the only difference being that the it’s amount at the end must be smaller than it’s amount at start.

07. Reduce = 
    SUMX(
        FILTER(
            'Opportunity History',
            [Amount]<[Amount at Start] &amp;&amp;
            [Pipe Dates are Valid at end]=1 &amp;&amp;
            [Has an open record Valid At Start] = 1
        ),
        [Amount at Start] - [Amount]
    )

Moved in

For an opportunity to fall into the “Moved in” category, the opportunity history record must be valid at the end, meaning it’s closed dates are within the timeframe and its validity within the pipeline timeframe, and its close date must be invalid at the beginning of the period. That’s why I created the following flag: “CloseDate at Start is Valid during period”.

CloseDate at Start is Valid during period = 
VAR SelectedCloseDate = [CloseDate at Start]
VAR Result = 
    IF(
        COUNTROWS(
            FILTER(
                'Opportunity History',
                SelectedCloseDate>=[Close Date Start] &&
                SelectedCloseDate<=[Close Date End]
            )
        )>0,
        1,
        0
    )
RETURN Result

04. Moved In = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [Record is Valid at end]=1 &&
            [Opportunity Is Created During Period]=0 && 
            [CloseDate at Start is Valid during period] = 0 
        )
    )

Moved out

06. Moved Out = 
    CALCULATE(
        [Amount],
        FILTER(
            'Opportunity History',
            [CloseDate is Valid during period] = 0 &&
            [Pipe Dates are Valid at end]=1 &&
            [Has an open record Valid At Start]=1
        )
    )

Putting it all together in a chart

Now that we have all flags up and working, the next step is to get this information on a beautiful waterfall chart, pretty much like the one suggested by Salesforce in their Einstein Analytics pipeline trend chart:

This chart below from Insight Squared is very similar as well.

All these Waterfall Categories measures are alike. However, notice that for Expand, I tweaked a bit the DAX measure, and I evaluated this expression for the table: [Amount]-[Amount at Start], not just the Amount. For Reduce, I used: [Amount at Start]-[Amount].

With this, we can get a nice overview of all categories in a Table visual, like this:

The next challenge is to put this into a Waterfall Chart. We can’t ask Power BI to consider these measures as rows, so I had to be creative.

First, I created a DAX table called “Pipeline Waterfall”, and as I wanted 12 rows (the number of different categories), I used the function GENERATESERIES. That’s why we have:

Pipeline Waterfall = GENERATESERIES(0,10)

Then, I gave names to these rows:

Waterfall Category Name = 
    SWITCH(
        'Pipeline Waterfall'[Row Number],
        0,"Pipeline Start",
        1,"New",
        2,"Reopen",
        3,"Expand",
        4,"Moved In",
        5,"Moved Out",
        6,"Reduce",
        7,"Won Not New",
        8,"Won New",
        9,"Lost",
        10,"Pipeline End",
        "OTHER"
    )

Lastly, I created a measure called “Pipeline Waterfall Amount”, which pull the Amount of each category and assigns it to the right row in the Pipeline Waterfall table.

Pipeline Waterfall Amount = 
VAR PipelineStart = [00. Pipeline Start]
VAR New = [01. New]
VAR Reopen = [02. Reopen]
VAR Expand = [03. Expand]
VAR MovedIn = [04. Moved In]
VAR MovedOut = [06. Moved Out]
VAR Reduce = [07. Reduce]
VAR ClosedWon = [08. Won Not New]
VAR ClosedWonNew = [09. Won and New]
VAR ClosedLost = [10. Lost]
VAR PipelineEnd = [11. Pipeline End]

VAR Result = 
    SWITCH(
        SELECTEDVALUE('Pipeline Waterfall'[Row Number]),
        0,PipelineStart,
        1,New,
        2,Reopen,
        3,Expand,
        4,MovedIn,
        5,-MovedOut,
        6,-Reduce,
        7,-ClosedWon,
        8,-ClosedWonNew,
        9,-ClosedLost,
        10,PipelineEnd
    )
RETURN Result   

The last step is to add a Waterfall visual in the report, add the Waterfall Category Name on category, and Pipeline Waterfall Amount on the Y axis, and… voilà!

Debugging

Notice that the Total on the waterfall chart visual is close to 0: -7E-9. The Total computes what’s left after all movements have been considered. It should always return 0. It means that my report logic is balanced, which is very important. If the result is different from 0, it means that there is a mistake somewhere with the flags logic.

I created a page called “Validation” to help debugging these flags. As we know, each opportunity must be balanced. To make sure everything is in order, I created a measure called Delta, which ensures that all movements are balanced.

Delta = 
    [00. Pipeline Start]
    +[01. New]
    +[02. Reopen]
    +[03. Expand]
    +[04. Moved In]
    -[06. Moved Out]
    -[07. Reduce]
    -[08. Won Not New]
    -[09. Won and New]
    -[10. Lost]
    -[11. Pipeline End]

I created a visual with this measure, and added an evaluation context for each opportunity by adding the Opportunity ID from the Opportunity table on rows. 

Let’s make a mistake on purpose on the “Expand” flag to see how this visual works. I commented the line [Has an open record Valid At Start] = 1 to get Deltas different from 0.

As we can see, many opportunities are imbalanced now. Let’s click on the first one to analyze why it’s imbalanced. The visual on the bottom of the page helps us see why it’s not balanced, and which flag doesn’t work.

Combined with the visual in the middle of the page, that contains the details of all related opportunity history records, we can solve the problem.

In this case, we can see that the first line is the record valid at end. For this reason, there is no reason for us to compute an amount of 1M€ as Expand.

From there, we might understand that for an opportunity to be expanded, it must have an open record valid at start, and therefore adding back the condition we deleted on purpose. The opportunity would then be balanced, and everything is back to normal.

Possible Enhancements

There are still many enhancements that could be done with this tool.

Speed

First, speed isn’t that great with high data volumes. I have about 650 thousand rows on the opportunity history table and the report is already slow.

The first speed related problem I see is that I had to create many DAX measures instead of DAX columns because most measures I created depend on a user selection of date timeframes for both pipeline date and close dates. If I could change this somehow, and convert most measures to columns, the report would be much faster I believe. My DAX knowledge is still very limited so it might well be that the functions I used in the measures are inefficient. Maybe I missed something simple. I’ll update this tool later if I notice a better way to build it in the future.

Drill down to details

Secondly, it would be nice to have a drill down feature that would allow the users to click on a category on the chart, and immediately see all opportunities that affect this category, and how. It’s possible to see the details by looking at the details page on the report and adding filter, but it’s not user friendly.

Visibility on deleted records

Note that this report doesn’t consider deleted records. Indeed, you could have really started with an amount greater than the amount shown on this report. More specifically, if you compare these results with results from a true pipeline snapshot, you might find discrepancies, as the waterfall chart I created can’t see deleted Salesforce records.

A nice to have feature would be to have another category called “Deleted Records”, using a snapshot table.

Flexibility on categories

We could also imagine a big DAX table with a configuration section, where you could pick which categories you need. For example:

• Split Closed Won into “Won Not New” and “Won New”
• Split Moved In into “Moved In from Past” and “Moved in from Future”
• Etc.