On Adding a Start Value to a Waterfall Chart in Power BI

Contents

First, let’s define the scope of the challenge: Imagine that we want to track the growth in your customer base over time. We create a measure to count customers based on their first order, identifying new customers for each month. But now, we want to create a visualization of this measure with a waterfall visual, and we get something like this: Figure 1 – Initial Waterfall chart in Excel (Figure by the Author) But we need something more. We need the initial value, which is the customer count as of the end of the previous year, and then add the number of new customers for each month to the total number of customers by the end of the reporting period. Something like this: Figure 2 – Second example in Excel with the Initial Total, be End of 2024, and the total be August 2025 (Figure by the Author) Unfortunately, we are unable to set one column as the start total in Power BI. Therefore, when adding the measure to a waterfall visual in Power BI, the result is similar to the one in the first example in Excel: Figure 3 – The initial approach in Power BI. Notice that Power BI automatically calculates the total in the Waterfall (Figure by the Author) Even when using a commercial custom visual like Zebra BI, the result isn’t as needed: Figure 4 – Example with a Zebra BI Visual (Figure by the Author) In this case, I added the previous year’s measure to set the start value. However, the visual calculates the deviation from the PY for each month and displays the Total based on these deviations. The result 141 for the previous year is calculated because Zebra BI detects that there are only 7 months of data for the current year, and it calculates the sum in the prior year only for those 7 months. In this specific case, it’s not what we need, as we are not interested in the deviation from the previous year, but rather in the growth starting at the end of the last year. Although this custom visual is extremely powerful, it isn’t helpful in our case. Expand the data model Developing the Measure(s)Conclusion References

Photo by Jeffrey Workman on Unsplash

Expand the data model

What we need now is a method not only to calculate, but to show the last month of the previous year when selecting a year, for example, 2025.

I have already solved this problem by adding a second date table and writing the corresponding measure.

You can find the full description of this solution and an explanation of how it works in the References section below.

The modified data model looks like this:

Figure 5 – Modified data model with the new Reporting date table in the middle (Figure by the Author)

This new date table enables us to work with the item selected in the “Selection Date” table and perform calculations with the date table.

To support this, I set both tables as “Date table”. I can still use the “Date” table as usual without restrictions.

Developing the Measure(s)

The next step is writing the measures.

First, I must calculate the start value, which is the number of customers by the end of the (previous) year.

To achieve this goal, I must calculate the sum of all rows for the previous year, but for all months:

New Customers (YE) =

    VAR SelYearPY = SELECTEDVALUE('Date'[Year])

    VAR Result = CALCULATE([New Customers]

                            ,REMOVEFILTERS('Date')

                            ,'Date'[Year] = SelYearPY

                            )

RETURN

    Result

The result is 280 for 2024.

You might wonder why I calculate the sum over the selected year instead of over the previous year.
The reason is that we want to show the result of this measure for December 2024 (When 2025 is selected). Please wait a moment until you see the results. These will help you understand it.

Now, we must develop a measure that returns the correct values depending on the month on the X-axis.

This should be the year-end value of the previous year for December 2024, or the count of new customers for each month of the current (selected) year. See the previous measure.

The measure we need must perform the following steps:

Get the selected year from the date table.
Calculate the first and last months to display in the report.
Calculate the result by removing the filter from the date table used in the Slicer (The table “Selection Date”).
Decide which result must be displayed for which month.

The last step is crucial.

As we remove the filter for the selected year from both the Date tables, we must control whether a result should be shown for each month. You will find this step in the SWITCH() part of the measure below.

This is the full measure:

Retail Sales Waterfall =

    VAR SelYear = SELECTEDVALUE('Selection Date'[Year])

    VAR MinYearMonth = SelYear * 100 + 1

    VAR MaxYearMonth = SelYear * 100 + 12

    VAR LastPYYearMonth = (SelYear - 1) * 100 + 12

    VAR ActualMK = CALCULATE(

                    MAX('Date'[MonthKey])

                    ,CROSSFILTER('Selection Date'[DateKey]

                                ,'Date'[DateKey]

                                ,None)

                                )

RETURN

SWITCH(TRUE()

    ,ActualMK = LastPYYearMonth

        ,CALCULATE([New Customers (YE)]

                    ,CROSSFILTER('Selection Date'[DateKey]

                                ,'Date'[DateKey]

                                ,None)

                    ,REMOVEFILTERS('Date')

                    ,'Date'[MonthKey] = (SelYear - 1) * 100 + 12

                    )

    ,ActualMK >= MinYearMonth

        && ActualMK <= MaxYearMonth

        ,CALCULATE([New Customers]

                            ,CROSSFILTER('Selection Date'[DateKey]

                                        ,'Date'[DateKey]

                                        ,None)

                                )

        ,BLANK()

    )

This is the result:

Figure 6 – Final result with the starting value by the end of the previous year (Figure by the Author)

Above the waterfall chart, you can see the numbers returned by the Measure.

As mentioned above, the year slicer in the top left corner doesn’t use the year column from the “Date” table, but from the “Selection Date” table. This is vital. The solution will not work when using the year column from the “Date” table.

Conclusion

The waterfall visual is excellent for showing the change in values from one element to another. An Element can be a month or something different. For example, you can find another article in the References section below, where I demonstrate how to modify a data model to display a business process with a waterfall visual in Power BI.

The solution shown here is a typical example of where I reused a previously developed approach to address a new challenge.

The beauty of this approach is that it allows me to continue using all Measures using the “regular” Date table without restriction and use the “Selection Date” table to add more functionality to the Report.

In this case, I added a feature to the waterfall chart that was previously unavailable.

I will not say that you always need two Date tables. Only add them when required. It makes no sense to add tables to a data model only for the sake of having them.

References

Here is my piece, where I show how I expanded my data model to be able to show more dates than selected:

Here, my piece where I explain how to change the data to show a business process with a waterfall chart:

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.