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

After we take 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 appropriate, my customers may want two completely different views:

1. See solely the outcomes in line with 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 completely different filters.
2. Create two pages for the 2 views, once more through the use of completely different filters.
3. Inform my customers to make use of the Calendar slicer to filter the info as wanted.

However I need to design my reviews as user-friendly as potential with out duplicating something.

Subsequently, I need 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 consists of Index columns for various intervals: Days, Weeks, Months, Quarters, and Years.

• The row with the present interval comprises a 0
• Rows for previous intervals include unfavourable 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 selection.

Now, I imported this desk into Energy BI

Increase the info 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 Route have to be set to Each because the column DateKey within the Date Filter desk shouldn’t be distinctive:

Subsequently, the filter path can be Date -> Date Filter solely, which won’t work.

I need to filter the Date desk by the Date Filter desk. Subsequently, I need to set the filter path to “Each”.

Outcome

Subsequent, I added a Slicer to the report:

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

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

Deciding on full intervals

The instance above is sensible when the choice have to be on the day degree.

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 might have just a few 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 have to be set to full intervals, like months or years?

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

Now the complete 12 months is proven, when solely the present knowledge must be proven:

You’ll be able to change the Date Filter desk in the identical technique to filter by months or different intervals.

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

Conclusion

This strategy is easy to implement and straightforward for Report shoppers to make use of.

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

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

In case you don’t have the Index columns as I do, you need to 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 additionally in lots of different conditions the place the consumer requires me to filter the info by dates relative to the present date.

This makes life simple when the “present date” shouldn’t be right this moment, however should comply with a particular logic—for instance, the earlier weekday.

OK, you’ll be able to add relative Filters to the report. In lots of instances, they’re sufficient.

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

References

Like in my earlier articles, I exploit the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset free of charge from Microsoft here.

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