Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Here are the two slicers:
This would be the end result that I would want based on the slicers.
Here is the table so you can copy it.
Base Table | |||
Item | Price | Price Start Date | Price End Date |
Apples | $ 1.98 | 1/1/2020 | 1/31/2020 |
Bananas | $ 0.88 | 3/1/2020 | 3/18/2020 |
Peaches | $ 0.56 | 12/1/2019 | 3/31/2020 |
Apples | $ 2.02 | 2/1/2020 | 2/29/2020 |
Blueberries | $ 5.99 | 2/10/2020 | 3/5/2020 |
Strawberries | $ 4.65 | 1/15/2020 | 2/2/2020 |
Thanks,
Dan
Solved! Go to Solution.
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
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
User | Count |
---|---|
123 | |
111 | |
99 | |
60 | |
60 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |