The right way to Filter for Dates, Together with or Excluding Future Dates, in Semantic Fashions

Think about that we’ve got three measures:

1. Gross sales Quantity
2. Gross sales Quantity Finances
3. Gross sales Quantity PY

After we have a look at a desk with these numbers, we see this (assuming that we’ve got 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 in line with the present knowledge
2. Together with the Finances 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 by utilizing totally 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 experiences as user-friendly as doable 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 contains Index columns for various durations: Days, Weeks, Months, Quarters, and Years.

• The row with the present interval comprises a 0
• Rows for previous durations comprise destructive numbers
• Rows for futures durations comprise constructive numbers

Let’s see the rows for a small pattern realized in SQL by utilizing 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 every other language of your alternative.

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 should 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 collection 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 durations

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 Finances or PY.

Think about on the 5^th or 6^th day of the month. You could have only some days of Gross sales, however you examine it to the Finances of the entire month. This may be deceptive.

However what occurs when the filter should be set to full durations, 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 approach to filter by months or different durations.

This may be finished 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 shoppers to make use of.

It avoids writing DAX code and depends completely on data within the Date desk.

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

In the event you don’t have the Index columns as I do, you should utilize a distinct strategy to generate the Date Filter desk. For instance, use a perform 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 shopper 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 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 take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without cost from Microsoft right here.

The Contoso Knowledge can be utilized freely beneath the MIT License, as described on this doc. I modified the dataset to shift the info to modern dates.