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.

Introduction
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.
Approach
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:
In short: I need a copy of my Date table and link it to my primary Date table.
Then, I use the copied Date table for the Slicer and the primary Date table for the chart.
I can use the CROSSFILTER() function to turn off the relationship between the copied Date table and the primary Date table to show all months of the selected year.
So far, so good.
But now I have to figure out how to calculate the correct values at the right point.
Way to the solution (Actuals)
The first step is to make sure that I can select one month from the Slicer using the copied Date table and use it to define the year for reporting the monthly results.
The challenge here is that without any logic, I will end up seeing only one data point for the selected month:

I must add some code to the Measure to correct this to see all months:
Retail Sales (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
RETURN
CALCULATE([Sum Retail Sales]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
First, I have to determine the selected year.
Then I use CALCULATE() to add a filter to the selected year. But in addition, I use the CROSSFILTER() function to turn off the relationship between the copied Date table and the primary Date table.
This is the intermediary result:

Now I get the same result, regardless of which month I select in the Slicer. The result changes only when I choose a month from a different year.
The next step is to add the logic to calculate the Actual Sales results only up to the selected month.
For example:
- I select May, and I see the Sales from January to May
- I select February, and I see only January and February
In this case, I cannot add a further Filter to CALCULATE() like this:
Retail Sales (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
RETURN
CALCULATE([Sum Retail Sales]
,'Date'[Year] = SelYear
,'Date'[Date] < LastSelDate
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
This filter would overwrite any filter from the Date table and return the same value for all months.
I need to ensure that no result is calculated after the selected month.
This is what I came up with:
Retail Sales (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
RETURN
IF(ActiveDate >= DATE(SelYear, 1, 1) &&
ActiveDate <= LastSelDate
,CALCULATE([Sum Retail Sales]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
)
First, I determine the last selected date from the copied Date table ‘Selection Date’. For example, when I choose May 2022, I will get May 31. 2022. For April, I will get April 30. 2022.
Next, I get the last date of the current Filter context in the Line Chart. As I use the primary Date table in the Visual, I can get it from there.
But, as I turn off the Relationship with the copied Date table, I must restrict the Year to the selected year from the Slicer. This is why I must add the filter ‘Date'[Year] = SelYear.
Lastly, I use an IF to determine if I must return a value.
Now, the Measure returns only results up to the selected month.
As you have seen above, my data contains up to Max 2022.
But when I select March 2022, I get this result:

Next step – Calculate the Forecast
Now, I must calculate the forecast starting from the Selected Month.
I can take the previous Measure and change the IF() to start from the selected month until the end of the year:
Retail Sales Forecast (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
RETURN
IF(ActiveDate >= LastSelDate &&
ActiveDate <= DATE(SelYear, 12, 31)
,CALCULATE([Retail Sales Forecast]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
)
As you can see, I only changed the IF() statement to start at the selected month and finish at the actual year’s end.
Now, we are near the final result:

But now I have this gap in the line.
Remember that I need to have one continuous line?
To achieve this, I need to make one little change to my Measure to calculate the Forecast: At the point of the selected month, I must return the Actual Sales number.
The modified Measure is the following:
Retail Sales Forecast (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
RETURN
SWITCH(TRUE()
,ActiveDate = LastSelDate
,[Sum Retail Sales]
,ActiveDate >= LastSelDate &&
ActiveDate <= DATE(SelYear, 12, 31)
,CALCULATE([Retail Sales Forecast]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
)
You can see that I exchanged the IF() with a SWITCH() and added a new condition to check if "ActiveDate" is equal to "LastSelDate".
The last change is to change the line for the Forecast to a dashed line, and the result is as expected:

What if we don’t have Sales?
So far, so good.
But what happens when the user chooses a month after the last month with Actual Sales?

My client said: Well, my users can understand this. So, no problem. Leave it as it is.
But for this Article, I want to give you a possible solution: When no data for Actual Sales exists, return the Forecast.
Let’s translate this to an additional condition for the SWITCH():
Retail Sales Forecast (Using correct date) =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
VAR Forecast = CALCULATE([Retail Sales Forecast]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
RETURN
SWITCH(TRUE()
,ISBLANK([Retail Sales (Using correct date)]), Forecast
,ActiveDate = LastSelDate
,[Sum Retail Sales]
,ActiveDate >= LastSelDate &&
ActiveDate <= DATE(SelYear, 12, 31)
,Forecast
)
Look at the first condition of SWITCH: ISBLANK([Retail Sales (Using correct date)]), Forecast
The result is not exactly as originally needed:

What is not possible is to close the gap between May and June.
The reason is that as we evaluate the Retail Sales Measure, we add the Forecast only when the Retail Sales Measure is blank. Therefore, we have no possibility of adding the missing value.
The data doesn’t exist at this point.
So, the only way to get one continuous line is to freeze the data, to stay as is, even when selecting a month after the last month with Actual data.
For this, I followed this process:
- Check if the month after the following month contains a Sales result
- If yes, return the Sales result for the selected month
- If no, then get the last Sales value and return it
- If no Sales data exists for the current month, return the Forecast result
To achieve this, I need a Measure to get the Sales for the Month after the current Month:
Sales next Month =
VAR LastActDate = CALCULATE(MAX('Date'[Date])
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
VAR Result = CALCULATE([Sum Retail Sales]
,'Date'[Date] > EOMONTH(LastActDate, 0)
&& 'Date'[Date] <= EOMONTH(LastActDate, 1)
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
RETURN
IF(ISBLANK([Retail Sales (Using correct date)]) = FALSE()
,Result)
Usually, this Measure would be much easier to write.
But because of the specific requirements for the solution, I need to do it this way.
First, I have to get the last date of the current month à Variable LastActDate
Then I get the Sales results for the dates after the variable LastActDate (> EOMONTH(‘Date'[Date], 0) and before or at the last Date of the following Month (<= EOMONTH(‘Date'[Date], 1).
But I only return this value when the Measure used to return the Actual Sales value returns no data (ISBLANK()).
Now, I can use this Measure to check if the current Month in the Visual is the last Month with Actual Sales data. If yes, return the last known Sales Data. Afterward, return Forecast data:
Retail Sales Forecast (Using correct date) Full =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
VAR Forecast = CALCULATE([Retail Sales Forecast]
,'Date'[Year] = SelYear
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None
)
)
VAR LastSalesDate = CALCULATE( MAX('Retail Sales'[Date])
,REMOVEFILTERS('Date')
)
VAR LastMonthSales = CALCULATE([Sum Retail Sales]
,REMOVEFILTERS('Date'[Date])
,'Date'[Date] > EOMONTH(LastSalesDate, -1)
&& 'Date'[Date] <= EOMONTH(LastSalesDate, 0)
)
RETURN
SWITCH(TRUE()
,ISBLANK([Retail Sales (Using correct date)]) = FALSE() && ISBLANK([Sales next Month]) = TRUE(), [Retail Sales (Using correct date)]
,ISBLANK([Retail Sales (Using correct date)]), Forecast
,ActiveDate = LastSelDate
,[Sum Retail Sales]
,ActiveDate >= LastSelDate &&
ActiveDate <= DATE(SelYear, 12, 31)
,Forecast
)
As I have to check for multiple cases, I use SWITCH() to decide which value I return.
When I select June 2022, or afterward, I always get this result:

Please consider that I must use the Month/Year column in the chart. I need this column, as the Measure must know which month in which year the current position is. Firstly, it was enough to know in which month I am, As I take the year from the selection in the Slicer.
In this case, this wasn’t possible anymore. So, I must change the used column for the months.
The big problem with this Measure is that it takes almost three seconds to calculate the result.
While the first Measure needs less than one second to complete the calculation:

The CROSSFILTER() Problem
The Microsoft documentation for CROSSFILTER() states in the Remarks section:
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Unfortunately, this critical point is not mentioned in the Dax.guide documentation for CROSSFILTER().
I encountered this issue on a data model of one of my clients.
If you implement RLS in your Data model, the above solution will no longer work.
An alternative to the CROSSFILTER() function is using ALLEXCEPT().
With ALLEXCEPT(), we can remove all filters from a table except for the filter on one or more columns of that table.
We can use ALLEXCEPT() to replace CROSSFILTER() while retaining the filter on the Year column of the ‘Selection Date’ table.
The Sales Measure will look like this:
[Retail Sales (Using correct date)] =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR LastSelDate = MAX('Selection Date'[Date])
VAR ActiveDate = CALCULATE(MAX('Date'[Date])
,ALLEXCEPT('Selection Date', 'Selection Date'[Year])
)
RETURN
IF(ActiveDate >= DATE(SelYear, 1, 1) &&
ActiveDate <= LastSelDate
,CALCULATE([Sum Retail Sales]
,ALLEXCEPT('Selection Date', 'Selection Date'[Year])
)
)
So, why we don’t use ALLEXCEPT() to begin with?
This version is much shorter and easier to understand than the version with CROSSFILTER().
Well, let’s look at the performance of the two versions.
Firstly, let’s look at the performance of the version with CROSSFILTER():

Now, let’s change the Measure in DAX Studio to the Version with ALLEXCEPT() and measure the performance:

As you can see, the Query takes more than double the time.
Moreover, the Storage Engine (SE) part shrinks from 46.7% to 16.7 %. This indicates that the Version with ALLEXCEPT() is less efficient than the version with CROSSFILTER().
The main reason is that the relationship between the ‘Selection Date’ and the ‘Date’ table remains active, and these two tables are joined together to get the result.
And the Formula Engine (FE) processing time is much higher than before.
You can read more about why this isn’t good in my Article on this topic:
So the approach with CROSSFILTER() should be used, as long as we don’t are constrained by the statement mentioned above in the Microsoft documentation.
Conclusion
Using the approach with the two Date tables offers great possibilities to enhance the Reporting capabilities.
But it introduces some complexity when calculating specific results, like the Sales for the following month.
Anyway, I recommend exploring this model and how it can give you previously unknown ways to develop solutions.
Until today, I built multiple solutions using this approach, which I previously thought impossible or very complex to develop or understand.
The requirements for the solution shown here were very particular, but I learned a lot while developing the complete solution.
Lastly, knowing the restrictions when using CROSSFILTER() and the alternative available is important.
But at the same time, it’s important to know that the alternative is less efficient.

References
I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.
The Contoso Data can be freely used under the MIT License, as described here.
I modified the dataset to contain only the Retail Sales table and added a derived table for the Forecast data.
You can support my work, which I do during my free time, through
https://buymeacoffee.com/salvatorecagliari
Or scan this QR Code:

Any support is greatly appreciated and helps me find more time to create more content for you.
Thank you a lot.