Forum Discussion
Auto Filter Based on Slicer Date
I have a slicer that is limiting a data set to the most recent "as of date" the table has the below structure. I need to further limit the output in my visuals to display only totals with an OpenDate month that is the month of the AsOfDate.
For example, using the below table I want to get the SUM of Amount where AsOfDate is 3/30/2022 and the OpenDate month is 3/2022.
Tables are using direct query.
| AsOfDate | OpenDate | Amount |
| 3/30/2022 | 3/01/2022 | 50.00 |
| 3/30/2022 | 3/4/2022 | 75.00 |
| 3/30/2022 | 1/10/2022 | 100.00 |
| 3/29/2022 | 1/12/2022 | 200.00 |
HI Anonymous,
You can add a measure to extract the current slicer selection and compare it with the 'open date' field values to return flag, then you can use this measure on the 'visual level filter' to filter records based on the flag.
Applying a measure filter in Power BI - SQLBI
flag = VAR currSelected = MAX ( Table[AsOfDate] ) VAR currOpenDate = MAX ( Table[OpenDate] ) RETURN IF ( currOpenDate <= currSelected, "Y", "N" )Regards,
Xiaoxin Sheng
3 Replies
- danextian
Super User
Hi Anonymous ,
Have you tried using another date slicer, this time, for OpenDate? HI Anonymous,
You can add a measure to extract the current slicer selection and compare it with the 'open date' field values to return flag, then you can use this measure on the 'visual level filter' to filter records based on the flag.
Applying a measure filter in Power BI - SQLBI
flag = VAR currSelected = MAX ( Table[AsOfDate] ) VAR currOpenDate = MAX ( Table[OpenDate] ) RETURN IF ( currOpenDate <= currSelected, "Y", "N" )Regards,
Xiaoxin Sheng
