Close Menu
    AI Tools

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

    AwaisBy No Comments5 Mins Read0 Views
    How to Filter for Dates, Including or Excluding Future Dates, in Semantic Models

    Imagine that we have three measures:

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

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

    Figure 1 – Starting point. Notice that the Budget and the PY data go beyond the current data, which exists only until the end of July 2025 (Figure by the Author)

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

    1. See only the results according to the existing data
    2. Including the Budget data and the PY data

    I have multiple ways to do it:

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

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

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

    Create the Date Filter table

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

    • The row with the current period contains 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 using the DayIndex column:

    Figure 2 – Sample rows from the Date table (Figure by the Author)

    Using this column, I constructed a table with an additional column to serve as a Slicer.

    I took all rows with an Index up to 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”.

    Figure 3 – Complete selection for the Date Filter table (Figure by the Author)

    I did it with SQL, but you can do it in Power Query or any other language of your choice.

    Now, I imported this table into Power BI

    Expand 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 – Creation of the Relationship between the new Date Filter and the Date table (Figure by the Author)

    The Cross-filter Direction must be set to Both as the column DateKey in the Date Filter table is not unique:

    Figure 5 – Relationship with the  Cross-filter direction set to both (Figure by the Author)

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

    I want to filter the Date table by the Date Filter table. Therefore, I must set the filter direction to “Both”.

    Result

    Next, I added a Slicer to the report:

    Figure 6 – The new Slicer set a Tile for the DateFilter column (Figure by the Author)

    After all the preparation, the Slicer allows the selection of the data as needed:

    Figure 7 – No future numbers are shown when “Current Data only” is selected (Figure by the Author)
    Figure 8 – All data is shown when “Future Data included” is selected (Figure by the Author)

    The users can use this slicer to choose which data they want to see, regardless of the selection in the Calendar slicer.

    Selecting full periods

    The example above is practical when the selection must be at the day level.

    This is useful especially when comparing the current data with the Budget or PY.

    Imagine on the 5th or 6th day of the month. You have only a few days of Sales, but you compare it to the Budget of the whole month. This can be misleading.

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

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

    Figure 9 – Query to set the whole year for the DateFilter column (Figure by the Author)

    Now the entire year is shown, when only the current data should be shown:

    Figure 10 – The whole year is shown when using the modified query from above (Figure by the Author)

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

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

    Conclusion

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

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

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

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

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

    This makes life easy when the “current date” is not today, but must follow a specific logic—for example, the previous weekday.

    OK, you can add relative Filters to the report. In many cases, they are enough.

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

    References

    Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

    The Contoso Data can be used freely under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.

    Share.

    Related Posts

    Leave A Reply