Combining Actuals and Forecasts in one continuous Line in Power BI | by Salvatore Cagliari | Aug, 2023


In several businesses, we have the Actual Sales and Forecasts. We can add these numbers to one line chart and see two lines. But one of my clients asked me if he can have one continuous line with the Actuals until the selected month and the Forecast for all months afterward. Here is how I did it.

Photo by Carlos Muza on Unsplash

To set the context, let’s look at the following picture:

Actuals and Forecast with a line chart (Figure by the Author)
Figure 1 — Actuals and Forecast with a line chart (Figure by the Author)

For this chart, I just entered a few numbers in Excel and created a line chart from these numbers.

You can see that the Actual and Forecast Sales lines are diverging from each other, which would be as expected.

While this is OK in most cases, my client wanted to have a different view of his data:

Target view requested by my client (Figure by the Author)
Figure 2 — Target view requested by my client (Figure by the Author)

As you can see, the target is to create one continuous line. Starting with January, then going forward with the Actual numbers per Month until the selected Month (Set in a separate Slicer). Then continue with the Forecast numbers. But the cutover at the selected month must be connected to form one continuous line.

Moreover, the cutover point must move to the selected month when he changes the selection for the month.

Well, that sounded interesting.

At first, the request sounded familiar: I want to select a specific month, and I want to see the data for the entire year and use the chosen month to do a particular calculation.

I explained a solution to this in one of my past articles:



Source link

Leave a Comment