Machine Learning

How to Filter for Dates, Including or Excluding Future Dates, in Logical Models

nimda January 4, 2026

0 4 4 minutes read

How to Filter for Dates, Including or Excluding Future Dates, in Logical Models

Consider that we have three steps:

Selling Price

Sales Amount Budget

Sales Price PY

If we look at the table with these numbers, we see this (assuming we have current data until the end of July 2025):

Figure 1 – The starting point. Note that the Budget and PY data exceed the current data, which are only available until the end of July 2025 (Author's Image)

Although the results are good, my users may need two different views:

Only see the results according to the available data

Includes budget data and PY data

I have several ways to do it:

Create different Visuals with different filters.

Create two pages for two views, and by using different filters.

Tell my users to use the Calendar slicer to filter data as needed.

But I want to design my reports to be as easy to use as possible without repeating anything.

So, I want to add a Slicer so users can choose to include future data or only see current data.

Create a date filter table

My Date table includes Index columns for different time periods: Days, Weeks, Months, Quarters, and Years.

The row with the current time contains 0
Past tense lines contain negative numbers
Future timelines contain positive numbers

Let's see a few sample rows available in SQL using the DayIndex column:

Figure 2 – Sample rows from the Date table (Author's Image)

Using this column, I created a table with an additional column to act as Sis.

I took all the rows with Index up to 0 and marked them as “Current Data only”.

Then I merged (INION) all the rows from the Date table and marked it as “Future Data entered”.

Figure 3 – Complete selection of date filter table (Author's Image)

I did it in SQL, but you can do it in power query or any other language you like.

Now, I imported this table into Power BI

Extend the data model

After adding this table to Power BI, I created a new relationship between the new table and the Date table:

Figure 4 – Creating a Relationship between the new Date Filter and the Date table (Author's Figure)

Cross-filter Direction should be set to Both since the DateKey column in the date filter table is not unique:

Figure 5 – Relationship and direction of the filter set in both (Author's Image)

So, the filter direction will be Date -> Date Filter only, which will not work.

I want to filter the Date table by DateFilter table. So, I have to set the filter directive to “Both”.

The result

Next, I added a Slicer to the report:

Figure 6 – New Slicer sets DateFilter column Tile (Author Image)

After all processing, Slicer allows data selection as needed:

Figure 7 – No future numbers are displayed when “Current Data Only” is selected (Author's Figure)

Figure 8 – All data is displayed when “Future data included” is selected (Author's Figure)

Users can use this slicer to select what data they want to see, without selecting the selection in the Calendar slicer.

Full time selection

The example above works if the selection should be at the date level.

This is especially useful when comparing current data to budget or PY.

Think of 5^th or 6^th day of the month. You only have a few days to Sell, but you compare it to the budget for the whole month. This can be misleading.

But what happens if the filter should be set to full periods, such as months or years?

Here's a modified query to set a DateFilter column based on all years:

Figure 9 – Query to set the year for the DateFilter column (Author's Figure)

Now the whole year is displayed, where only current data should be displayed:

Figure 10 – An entire year is displayed when using a modified query from above (Author's Figure)

You can change the Date Filter table in the same way to filter by months or other times.

This can easily be done by sorting the rows in the Date table for the current date.

The conclusion

This method is easy to use and easy for Bika users to use.

It avoids writing DAX code and relies entirely on the information in the Date table.

The trick is to extend the data model to support the requirements with minimal effort.

If you don't have Index columns like I do, you can use a different method to generate a date filter table. For example, use a function to get the current date, such as GETDATE() in T-SQL or Now() in other languages.

I found Index columns to be very useful, not only in this situation but also in many other situations where the client needs me to filter data by dates relative to the current date.

This makes life easier if the “current date” is not today, but must follow some logical pattern—for example, a day of the previous week.

OK, you can add related Filters to the report. In most cases, they are enough.

In some cases, Index columns give me more flexibility.