**Question for Power BI users!
I am trying to add a Boolean slicer on my Power BI report. However, this Boolean value needs to filter different properties depending on the max date selected. I have a Calendar Table and Property List table as my dimension tables. "Same Store" means that a property has been owned for over 2 years.
Table below is just for explanation, not an actual table in the data model. Date column correlates with 'Calendar'[Date]. [Property] correlates with 'Property List'[Property].
| Date | Property | Acqusition | Age (yrs) | Same Store |
|---|---|---|---|---|
| 1/31/2020 | 1 | 2/27/2018 | 1.93 | False |
| 2/29/2020 | 1 | 2/27/2018 | 2.01 | True |
| 3/31/2020 | 1 | 2/27/2018 | 2.09 | True |
| 1/31/2020 | 2 | 3/15/2018 | 1.88 | False |
| 2/29/2020 | 2 | 3/15/2018 | 1.96 | False |
| 3/31/2020 | 2 | 3/15/2018 | 2.05 | True |
I would like a Slicer for "Same Store" properties, so I could select "True" with a selected date of 2/29/2020. Then, the report would filter to only property 1.
I've tried calculated columns and measures. Calculated columns aren't dynamic enough to take the date into consideration. Measures require aggregation, but this would be ideal. I could use a switch measure then. Here's one thing I've tried:
Same Stores =
var _maxdate = max('Calendar'[Date])
return
IF(
DATEDIFF('Property List'[Acquisition], _maxdate,YEAR) >= 2, --doesn't work since [Acquisition] isn't aggregated.
TRUE(),
FALSE()
)
I appreciate any insight into this!