How To Create Date Slicer In Power Bi

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.

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.