Auto Filter Based on Slicer Date

[Anonymous]

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

[Anonymous · Accepted solution]

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

[danextian]

Hi @Anonymous ,

Have you tried using another date slicer, this time, for OpenDate?

[Anonymous]

I did, it works but I would ideally like to default the slicer selection to the month of the 'AsOfDate' if that makes sense?