cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Filtering Table based on Valid After and Valid Before dates

Good morning experts!

I should probably be able to figure this out however I am hitting a wall. I have a base table pricing catalog guide. It lists out all of the different items that we are selling, the price and the validity dates. The table goes back several years as well as into the future. What I would like to do is be able to have a slicer or two called "Valid After Date" and "Valid Before Date" that a user would be able to put dates into and see all of the items in the pricing catalog that have active pricing between (and including) those dates. 

 

In order to better explain I made a very simple example of what I am trying to do. 

Here is my base table:

Base Table.jpg

Here are the two slicers:

Selectors.jpg

 

This would be the end result that I would want based on the slicers.

 Result.jpg

 

Here is the table so you can copy it.

Base Table
ItemPricePrice Start DatePrice End Date
Apples $  1.981/1/20201/31/2020
Bananas $  0.883/1/20203/18/2020
Peaches $  0.5612/1/20193/31/2020
Apples $  2.022/1/20202/29/2020
Blueberries $  5.992/10/20203/5/2020
Strawberries $  4.651/15/20202/2/2020

 

 

Thanks,

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Filtering Table based on Valid After and Valid Before dates

Create a measure like this and add to your table

Measure = 
var _min = date(2020,02,01)
var _max = date(2020,02,29)
return
 CALCULATE(Min(Sheet1[Price]),Sheet1[Price End Date]>=_min , Sheet1[Price Start Date]<=_max)

 

The min and max can come from your slicer

min = minx(slicer1,slicer1[Date])

max= maxx(slicer1,slicer1[Date])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Filtering Table based on Valid After and Valid Before dates

Create a measure like this and add to your table

Measure = 
var _min = date(2020,02,01)
var _max = date(2020,02,29)
return
 CALCULATE(Min(Sheet1[Price]),Sheet1[Price End Date]>=_min , Sheet1[Price Start Date]<=_max)

 

The min and max can come from your slicer

min = minx(slicer1,slicer1[Date])

max= maxx(slicer1,slicer1[Date])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Filtering Table based on Valid After and Valid Before dates

@amitchandak  - that works perfectly. Thank you!

 

Dan

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors