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.
To set the context, let’s look at the following picture:
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:
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:
This post originally appeared on TechToday.