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

First, let’s outline the scope of the problem: Think about that we wish to monitor the expansion in your buyer base over time.

We create a measure to rely prospects primarily based on their first order, figuring out new prospects for every month.

However now, we wish to create a visualization of this measure with a waterfall visible, and we get one thing like this:

However we want one thing extra.

We’d like the preliminary worth, which is the shopper rely as of the tip of the earlier 12 months, after which add the variety of new prospects for every month to the entire variety of prospects by the tip of the reporting interval.

One thing like this:

Sadly, we’re unable to set one column as the beginning complete in Energy BI.

Subsequently, when including the measure to a waterfall visible in Energy BI, the result’s much like the one within the first instance in Excel:

Even when utilizing a business customized visible like Zebra BI, the consequence isn’t as wanted:

On this case, I added the earlier 12 months’s measure to set the beginning worth. Nonetheless, the visible calculates the deviation from the PY for every month and shows the Whole primarily based on these deviations.

The consequence 141 for the earlier 12 months is calculated as a result of Zebra BI detects that there are solely 7 months of knowledge for the present 12 months, and it calculates the sum within the prior 12 months just for these 7 months.

On this particular case, it’s not what we want, as we’re not within the deviation from the earlier 12 months, however moderately within the progress beginning on the finish of the final 12 months.

Though this tradition visible is extraordinarily highly effective, it isn’t useful in our case.

Broaden the information mannequin

What we want now is a technique not solely to calculate, however to point out the final month of the earlier 12 months when choosing a 12 months, for instance, 2025.

I’ve already solved this downside by including a second date desk and writing the corresponding measure.

You’ll find the complete description of this resolution and an evidence of the way it works within the References part under.

The modified knowledge mannequin appears like this:

This new date desk permits us to work with the merchandise chosen within the “Choice Date” desk and carry out calculations with the date desk.

To assist this, I set each tables as “Date desk”. I can nonetheless use the “Date” desk as regular with out restrictions.

Creating the Measure(s)

The following step is writing the measures.

First, I have to calculate the beginning worth, which is the variety of prospects by the tip of the (earlier) 12 months.

To realize this aim, I have to calculate the sum of all rows for the earlier 12 months, however for all months:

New Prospects (YE) =

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

    VAR Consequence = CALCULATE([New Customers]

                            ,REMOVEFILTERS('Date')

                            ,'Date'[Year] = SelYearPY

                            )

RETURN

    Consequence

The result’s 280 for 2024.

You may marvel why I calculate the sum over the chosen 12 months as a substitute of over the earlier 12 months.
The reason being that we wish to present the results of this measure for December 2024 (When 2025 is chosen). Please wait a second till you see the outcomes. These will assist you to perceive it.

Now, we should develop a measure that returns the right values relying on the month on the X-axis.

This must be the year-end worth of the earlier 12 months for December 2024, or the rely of recent prospects for every month of the present (chosen) 12 months. See the earlier measure.

The measure we want should carry out the next steps:

1. Get the chosen 12 months from the date desk.
2. Calculate the primary and final months to show within the report.
3. Calculate the consequence by eradicating the filter from the date desk used within the Slicer (The desk “Choice Date”).
4. Determine which consequence have to be displayed for which month.

The final step is essential.

As we take away the filter for the chosen 12 months from each the Date tables, we should management whether or not a consequence must be proven for every month. You will see this step within the SWITCH() a part of the measure under.

That is the complete measure:

Retail Gross sales Waterfall =

    VAR SelYear = SELECTEDVALUE('Choice 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('Choice Date'[DateKey]

                                ,'Date'[DateKey]

                                ,None)

                                )

RETURN

SWITCH(TRUE()

    ,ActualMK = LastPYYearMonth

        ,CALCULATE([New Customers (YE)]

                    ,CROSSFILTER('Choice Date'[DateKey]

                                ,'Date'[DateKey]

                                ,None)

                    ,REMOVEFILTERS('Date')

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

                    )

    ,ActualMK >= MinYearMonth

        && ActualMK <= MaxYearMonth

        ,CALCULATE([New Customers]

                            ,CROSSFILTER('Choice Date'[DateKey]

                                        ,'Date'[DateKey]

                                        ,None)

                                )

        ,BLANK()

    )

That is the consequence:

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

As talked about above, the 12 months slicer within the high left nook doesn’t use the 12 months column from the “Date” desk, however from the “Choice Date” desk. That is important. The answer is not going to work when utilizing the 12 months column from the “Date” desk.

Conclusion

The waterfall visible is great for exhibiting the change in values from one factor to a different. An Aspect generally is a month or one thing completely different. For instance, you will discover one other article within the References part under, the place I reveal the way to modify a knowledge mannequin to show a enterprise course of with a waterfall visible in Energy BI.

The answer proven here’s a typical instance of the place I reused a beforehand developed method to deal with a brand new problem.

The fantastic thing about this method is that it permits me to proceed utilizing all Measures utilizing the “common” Date desk with out restriction and use the “Choice Date” desk so as to add extra performance to the Report.

On this case, I added a characteristic to the waterfall chart that was beforehand unavailable.

I can’t say that you simply all the time want two Date tables. Solely add them when required. It is not sensible so as to add tables to an information mannequin just for the sake of getting them.

References

Right here is my piece, the place I present how I expanded my knowledge mannequin to have the ability to present extra dates than chosen: