Power BI From and To Date Filtering with One Slicer

Power BI from and to date slicer

It happens that you might have two fields as From and To date (or Start and End date) in your dataset, and you want a date slicer in the report. The date slicer has to filter records in a way that the FROM and the TO dates are in the range of dates selected in the slicer. There are multiple ways of doing this. In this article and video, I’ll explain a simple but effective method for that. I have explained in another article, how this can be done using two date slicers, you can read that from here.


Sample dataset

Let’s assume you have an employment history table with FROM and TO dates.

Assume that we want to have a report like below;

As you can see in the screenshot above, the Date slicer filters the transactions. The FromDate and ToDate both fall inside the date range selected in the slicer.

Date Table

For this solution, you need a custom Date table. You can either create it using the Power Query script, or DAX script or just as simple as a calendar function to create it. Here is my example of the Date table;

It is also important that you mark this table as a Date table, and choose the Date field for it.

Another important thing to do is to turn off the auto date/time in the Power BI file options so that it won’t create extra unnecessary Date tables. You can find this under File > Options and Settings > Options

I have explained these steps and why they are necessary to be applied in my article here.

No relationship to the Date table

It is important not to create a relationship between the transaction table and this Date table.

That is perhaps against all the rules you’ve heard in Power BI modeling that the relationship is necessary for filtering tables. Those rules are correct, but for scenarios that you are filtering based on one field. In this case, we want the Date table to filter the Employment History table based on TWO fields; The FromDate and the ToDate fields. You cannot create two active relationships between these two tables, so you have to either create a role-playing dimension, which ends up with using this method I explained here with two Date tables, or the method I am about to explain here using DAX expressions for filtering.

DAX Measure to Filter

The key for this pattern is to use a DAX measure to filter the data instead of the relationship. This means we create a DAX measure, and then use it as a filter for the visual. Let’s first go through the steps of creating the measure.

The DAX measure you can see here is using two variables to find out the start and end of the date range in the slicer. Then the SelectedValue is used to get the current row’s FromDate and ToDate values and compare it with the selected range. In the end, it returns the value 1 if is in the range, and 0 if not.

InDateRange = 
var _rangeStart=FIRSTDATE('Date'[Date])
var _rangeEnd=LASTDATE('Date'[Date])
    SELECTEDVALUE('Employment History'[FromDate])>=_rangeStart
    SELECTEDVALUE('Employment History'[ToDate])<=_rangeEnd
    SELECTEDVALUE('Employment History'[ToDate])<>BLANK(),

To explain things a bit more in detail read this:

  • _rangeStart and _rangeEnd are variables that pick the selected date range from the slicer. To learn more about variables, read my article here.
  • The IF statement checks if the FromDate is after the _rangeStart and the ToDate is before the _rangeEnd.
  • As there might be Nulls (or blank) in the ToDate, there is a last check to see if the ToDate is not equal blank.

If this measure be added to the table visual, here is how it would work;

For the rows that are in the range, the measure will return 1 for them. and for the remaining rows, it will return 0.

This means you can use this measure just for filtering and not for showing in the visual.

Measure as a Visual Filter

We do not need the measure in the table visual, you can remove that. Instead, add the measure into the visual level filter.

Now set the rule as Equal to 1. This means only those rows that fits in the range;

And here you go, the report working like a charm!


Filtering based on two fields using one single dimension table can be done by filtering data using DAX measure, which is the pattern I explained here. Please note that if you want to filter only based on one field. For example, find all records that their ToDate falls in the selected date range, no matter if the FromDate is in the range or not, then you can do it in a much simpler way, but creating a relationship based on ToDate and the Date table. Because in this case that is the only field that needs filtering.

Study More

There are other articles that I strongly suggest to get a full grasp of how this pattern works;

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply