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.
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
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:
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:
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.