Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danb
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
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

@amitchandak  - that works perfectly. Thank you!

 

Dan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.