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

Think about that we’ve three measures:

1. Gross sales Quantity
2. Gross sales Quantity Price range
3. Gross sales Quantity PY

Once we have a look at a desk with these numbers, we see this (assuming that we’ve present knowledge till the tip of July 2025):

Whereas the outcomes are right, my customers would possibly want two totally different views:

1. See solely the outcomes based on the prevailing knowledge
2. Together with the Price range knowledge and the PY knowledge

I’ve a number of methods to do it:

1. Create separate Visuals with totally different filters.
2. Create two pages for the 2 views, once more through the use of totally different filters.
3. Inform my customers to make use of the Calendar slicer to filter the information as wanted.

However I wish to design my studies as user-friendly as doable with out duplicating something.

Due to this fact, I wish to add a Slicer so customers can select whether or not to incorporate future knowledge or solely see present knowledge.

Create the Date Filter desk

My Date desk contains Index columns for various intervals: Days, Weeks, Months, Quarters, and Years.

• The row with the present interval accommodates a 0
• Rows for previous intervals include adverse numbers
• Rows for futures intervals include constructive numbers

Let’s see the rows for a small pattern realized in SQL through the use of the DayIndex column:

Utilizing this column, I constructed a desk with a further column to function a Slicer.

I took all rows with an Index as much as 0 and marked them as “Present Knowledge solely”.

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

I did it with SQL, however you are able to do it in Energy Question or some other language of your alternative.

Now, I imported this desk into Energy BI

Develop the information mannequin

After including this desk to Energy BI, I created a brand new Relationship between the brand new desk and the Date desk:

The Cross-filter Path should be set to Each because the column DateKey within the Date Filter desk isn’t distinctive:

Due to this fact, the filter course can be Date -> Date Filter solely, which won’t work.

I wish to filter the Date desk by the Date Filter desk. Due to this fact, I need to set the filter course to “Each”.

End result

Subsequent, I added a Slicer to the report:

After all of the preparation, the Slicer permits the choice of the information as wanted:

The customers can use this slicer to decide on which knowledge they wish to see, whatever the choice within the Calendar slicer.

Choosing full intervals

The instance above is sensible when the choice should be on the day stage.

That is helpful particularly when evaluating the present knowledge with the Price range or PY.

Think about on the 5^th or 6^th day of the month. You’ve gotten only some days of Gross sales, however you evaluate it to the Price range of the entire month. This may be deceptive.

However what occurs when the filter should be set to full intervals, like months or years?

Right here, a modified question to set the DateFilter column based mostly on complete years:

Now your complete 12 months is proven, when solely the present knowledge ought to be proven:

You possibly can change the Date Filter desk in the identical approach to filter by months or different intervals.

This may be accomplished very simply by filtering the rows within the Date desk for the present date.

Conclusion

This strategy is simple to implement and simple for Report customers to make use of.

It avoids writing DAX code and depends fully on info within the Date desk.

The trick is to increase the information mannequin to assist the necessities with the least effort.

In the event you don’t have the Index columns as I do, you should use a distinct strategy to generate the Date Filter desk. For instance, use a operate to get the present date, like GETDATE() in T-SQL or Now() in different languages.

I discovered the Index columns very helpful, not solely on this case but in addition in lots of different conditions the place the shopper requires me to filter the information by dates relative to the present date.

This makes life simple when the “present date” isn’t as we speak, however should observe a selected logic—for instance, the earlier weekday.

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

In different circumstances, the Index columns give me extra flexibility.

References

Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset totally free from Microsoft here.

The Contoso Knowledge can be utilized freely below the MIT License, as described in this document. I modified the dataset to shift the information to up to date dates.