From and To Date Slicers in Power BI: Filtering Based on Two Fields

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Power BI date slicers can be used to filter based on one field. However, what if in your dataset, you have a FROM and TO (or Start and End) Date? In that case, you’d most probably want to use the slicer to filter the report in such a way that it shows all data in between the start and end date. In this short blog article, I’ll explain how you can achieve this simply. If you want to learn more about Power BI, Read Power BI from Rookie to Rock Star.

Sample Model

The sample dataset used here is the AdventureWorksDW Excel file which can be downloaded from here. The tables that you need to load are FactInternetSales, DimCustomer, and DimProduct.

I have also renamed the OrderDate to Start Date, renamed the ShipDate to End Date, and removed the DueDate for this example.

I’ve created a sample report below:

My goal is to have a slicer for filtering all records that their transactions are starting from a given date and ending at a given date

Two Slicers

As you’ve probably noticed, I am using the default date table in Power BI that is automatically created for the “Start Date” and “End Date” fields. The same approach would work for custom date tables too.

I have added one slicer for Start Date

Then I have changed the type of it, using this option:

To: After

Now my Start Date slicer looks like this:

I can adjust the from date. But the to date is always the maximum.

Now, I add the second slicer and add the End Date to it

I change the type of the End Date Slicer to “BEFORE”

And now, we have the BEFORE and AFTER slicer simply working for Start and End date:

Summary

The simple method that I showed here works for both custom date table or the default date table. This method uses two slicers, one with the setting of AFTER and the other with the setting of BEFORE. Using this method, you can get all transactions that fall in between those two separate transaction date fields. Have you ever had a similar issue? Hope this resolves that. If not, share your challenge below, and I’d be happy to find a solution for you.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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.

4 thoughts on “From and To Date Slicers in Power BI: Filtering Based on Two Fields

  • Hi,

    I have a problem I am trying to solve which is similar to this but not quite solvable by this solution I believe. I have a table which contains a list of projects with a start and end date and a number of people needed to deliver the project. I need to generate a visual which sums the volume of people needed on these projects looking into the future to create a demand line. I also need with ability to filter this with a slider and this demand to increase or decrease as projects start and end.

    Is this possible?

    Any advice would be greatly appreciated

    Thanks, Neil

  • Hi Reza,

    Thanks for the blog! I regularly follow this

    I have a similar issue.
    I have a Table with StartDate and EndDate columns. I need a DateFilter with From and To fields which would apply both on the StartDate and EndDate
    Meaning I need the records whose StartDate would fall in the ‘From’ and ‘To’ window and the same thing i.e. those records with EndDate falling under ‘From’ and ‘To’ window

    Would the above solution work

    Thanks in advance

    • Hi.
      Do you have a sample data rows of this table you can copy/paste here for me to see?
      The period from start to end date is not one day to check in between two fields. I need to see the data rows to get a feeling of what you are after.
      Cheers
      Reza

Leave a Reply to Neil Sim Cancel reply

Your email address will not be published. Required fields are marked *