The usual advice for Semantic Models date filtering is a stack of visual-level filters and duplicated report pages. That falls over the moment future data shows up where it shouldn’t, whether that is a budget or a “Prior Year” (PY) projection. If a client has ever told you the “Budget” line is showing months that haven’t happened yet, you have met this problem already.
Budget vs. Actual visuals will happily draw data past today. The budget rows do exist, so the numbers are right, but the chart reads as broken. The common fix is a DAX measure that calls TODAY() on every evaluation, and that gets expensive fast. I would rather fix the model.
Where Semantic Models date filtering goes wrong
Say you have three measures on the page: Sales Amount, Budget, and Sales PY. The Budget and PY lines run off into the future while Actuals stop at July, and the visual looks wrong even though nothing is. The fix I reach for is a dedicated “Date Filter” table, which keeps the logic in one place instead of hardcoded into every visual.
My Date tables usually carry an Index column: negative for the past, zero for current, positive for the future. If non-linear data structures are new to you, my notes on handling data the right way cover some of the background.
Building the Date Filter table
What we want is a physical table in the model, not a transient patch. It maps each DateKey to one of two views: “Current Data only” and “Future Data included.” Here is the SQL I use to build it before anything reaches the BI layer:
-- Step 1: Current data (Index <= 0)
SELECT DateKey, 'Current Data only' AS DateFilter
FROM DimDate
WHERE DayIndex <= 0
UNION ALL
-- Step 2: Everything included
SELECT DateKey, 'Future Data included' AS DateFilter
FROM DimDateWith the table in place, picking “Current Data only” leaves the model unable to see dates past today. The restriction lives in the data, so there is nothing to recompute per visual.
The relationship gotcha
After importing the table, link it to your main Date table and set the Cross-filter direction to Both. I normally tell people to stay away from bi-directional filters, since they cause race conditions in your filter context, but this is the case that needs one. The DateFilter table is not unique, so the filter has to travel back to the Date table before it can restrict the fact rows.
Microsoft’s documentation on data reduction goes deeper on modeling for performance.
If Semantic Models date filtering is eating your dev hours, I can take it off your hands. I have been working on WordPress and messy data integrations since the 4.x days.
What you end up with
Once this is wired up, drop the DateFilter column into a slicer and the user gets a toggle. There are no DAX measures to refactor and no page-level filters to keep in sync, because the model handles it. That matters more once you get into work like supply chain data science, where getting the dates wrong changes the answer.
