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.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
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 Neil
Unfortunately, it is hard to understand the question. Can you elaborate more? sample rows of your table would be helpful too.
Cheers
Reza
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
Hi , I have some common date table with date slicer(between) .. Based on from date and to date chosen ,i need to filter on 2 fields in table which is start date and end date.
Example L
So If i select date slicer with startdt as 2019-01-01 and end date as 2020-31-12, I would like to get the retrieve value as product 1 .
I can get selected slicer value in measures with min & max. How we can apply that measure selected in the calculation logic based on the below fields in table
Table :
Product Startdt enddt
1 2020-01-01 2025-01-01
2 2021-01-01 2029-01-01
Thx in advance!
Do you mean; you want the entire period of selection (start to end) to fall in one record’s start and end date’s range, and if that is so, then show the product?
Hi Everyone, I’ve got data set with the client name and their service start and end date (end date can be futuristic as well). Now i want to know the total number of active clients. Active clients means, those clients who’s 1. end date is greater that today 2. End date is missing and lastly. How do i put slicer filter from where i filter the table for last three months active clients.
Hi.
Your scenario is the subscription pattern, which I explained here in detail.
Cheers
Reza