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 need to monitor the expansion in your buyer base over time.

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

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

However we’d like one thing extra.

We want the preliminary worth, which is the client depend as of the top of the earlier yr, after which add the variety of new clients for every month to the whole variety of clients by the top 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 just 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 yr’s measure to set the beginning worth. Nevertheless, the visible calculates the deviation from the PY for every month and shows the Complete primarily based on these deviations.

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

On this particular case, it’s not what we’d like, as we aren’t within the deviation from the earlier yr, however somewhat within the progress beginning on the finish of the final yr.

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

Develop the information mannequin

What we’d like now is a technique not solely to calculate, however to point out the final month of the earlier yr when choosing a yr, for instance, 2025.

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

You could find the complete description of this resolution and an evidence of the way it works within the References part beneath.

The modified knowledge mannequin appears like this:

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

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

Creating the Measure(s)

The subsequent step is writing the measures.

First, I need to calculate the beginning worth, which is the variety of clients by the top of the (earlier) yr.

To attain this purpose, I need to calculate the sum of all rows for the earlier yr, however for all months:

New Clients (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 yr as a substitute of over the earlier yr.
The reason being that we need 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 enable you to perceive it.

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

This needs to be the year-end worth of the earlier yr for December 2024, or the depend of recent clients for every month of the present (chosen) yr. See the earlier measure.

The measure we’d like should carry out the next steps:

1. Get the chosen yr 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. Resolve which consequence should be displayed for which month.

The final step is essential.

As we take away the filter for the chosen yr from each the Date tables, we should management whether or not a consequence needs to be proven for every month. You’ll find this step within the SWITCH() a part of the measure beneath.

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’ll be able to see the numbers returned by the Measure.

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

Conclusion

The waterfall visible is great for exhibiting the change in values from one component to a different. An Component generally is a month or one thing totally different. For instance, you will discover one other article within the References part beneath, the place I exhibit tips on how to modify an information 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 great 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 function to the waterfall chart that was beforehand unavailable.

I cannot say that you simply at all times want two Date tables. Solely add them when required. It is not sensible so as to add tables to a knowledge 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: