Filter for Dates, Including or Excluding Future Dates, in Semantic Models

Imagine that we have now three measures:

1. Sales Amount
2. Sales Amount Budget
3. Sales Amount PY

Once we take a look at a table with these numbers, we see this (assuming that we have now current data until the tip of July 2025):

While the outcomes are correct, my users might need two different views:

1. See only the outcomes in line with the present data
2. Including the Budget data and the PY data

I even have multiple ways to do it:

1. Create separate Visuals with different filters.
2. Create two pages for the 2 views, again by utilizing different filters.
3. Tell my users to make use of the Calendar slicer to filter the info as needed.

But I need to design my reports as user-friendly as possible without duplicating anything.

Due to this fact, I need so as to add a Slicer so users can select whether to incorporate future data or only see current data.

Create the Date Filter table

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

• The row with the present period comprises a 0
• Rows for past periods contain negative numbers
• Rows for futures periods contain positive numbers

Let’s see the rows for a small sample realized in SQL by utilizing the DayIndex column:

Using this column, I constructed a table with an extra column to function a Slicer.

I took all rows with an Index as much as 0 and marked them as “Current Data only”.

Then I appended (UNION) all rows from the Date table and marked them as “Future Data included”.

I did it with SQL, but you may do it in Power Query or some other language of your alternative.

Now, I imported this table into Power BI

Expand the info model

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

The Cross-filter Direction should be set to Each because the column DateKey within the Date Filter table just isn’t unique:

Due to this fact, the filter direction can be Date -> Date Filter only, which can not work.

I need to filter the Date table by the Date Filter table. Due to this fact, I have to set the filter direction to “Each”.

Result

Next, I added a Slicer to the report:

In spite of everything the preparation, the Slicer allows the collection of the info as needed:

The users can use this slicer to decide on which data they wish to see, whatever the selection within the Calendar slicer.

Choosing full periods

The instance above is practical when the choice should be on the day level.

This is beneficial especially when comparing the present data with the Budget or PY.

Imagine on the 5^th or 6^th day of the month. You have got only a couple of days of Sales, but you compare it to the Budget of the entire month. This could be misleading.

But what happens when the filter should be set to full periods, like months or years?

Here, a modified query to set the DateFilter column based on whole years:

Now all the yr is shown, when only the present data must be shown:

You possibly can change the Date Filter table in the identical solution to filter by months or other periods.

This could be done very easily by filtering the rows within the Date table for the present date.

Conclusion

This approach is easy to implement and straightforward for Report consumers to make use of.

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

The trick is to increase the info model to support the necessities with the least effort.

Should you don’t have the Index columns as I do, you should utilize a unique approach to generate the Date Filter table. For instance, use a function to get the present date, like GETDATE() in T-SQL or Now() in other languages.

I discovered the Index columns very useful, not only on this case but in addition in lots of other situations where the client requires me to filter the info by dates relative to the present date.

This makes life easy when the “current date” just isn’t today, but must follow a selected logic—for instance, the previous weekday.

OK, you may add relative Filters to the report. In lots of cases, they’re enough.

In other cases, the Index columns give me more flexibility.

References

Like in my previous articles, I exploit the Contoso sample dataset. You possibly can download the ContosoRetailDW Dataset without cost from Microsoft here.

The Contoso Data could be used freely under the MIT License, as described on this document. I modified the dataset to shift the info to contemporary dates.