Filtering based on Date Selection

[Tihannah]

Let's say I have a load of fruit and the dates we stopped stocking them in addition to current in a Fruit Dimension table.

Apples -removed 1/15/22

Oranges - removed 3/5/22

Grapes - removed 4/11/22

Bananas - Active

Pears - Active

I currently have a filter on all removed items, but want those items to show up if the month selected is equal to or less than the date they were removed from inventory. I do not want them to show in the months following the removal date, but if I used the Removed filter, I lose everything historically.  How can I write a dax or create a filter that only removes them the month AFTER the month of stock date ending? 

i.e. If I select January 2022, I see my inventory of apples, but if I select February 2022, I would not see the apples.

[Tihannah · Accepted solution]

I was able to resolve this adding a Max Date for the Month of Date Removed and then a Flag if Date Removed was >= the Max Date. I then filtered on the Flag. Thanks.

[danextian]

Hi @Tihannah ,

I'd use a different Dates table to achieve this then create this measure to count the rows of Date Removed with which are either blank or less than or equal the currently selected period.

Is Active = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALL ( Dates ),
    FILTER (
        ALL ( 'Table'[Date Removed] ),
        'Table'[Date Removed] <= MAX ( Dates[Date] )
            || 'Table'[Date Removed] = BLANK ()
    )
)

Please refer to this PBIX for your reference -https://drive.google.com/file/d/1lLJC0_SJMfDKYqT-q3DwFVkyTmx8aq_e/view?usp=sharing 

[Tihannah]

I tried changing this = 

'Table'[Date Removed] <= MAX ( Dates[Date]

to >=, but then when I select the month that they were removed, i.e. January - the apples disappear. 

[Tihannah]

This appears to be operating backwards to my goal. When I select January, the items removed in March and April do not show, and when I select April, the item from January is still there? I will be showing inventory counts, and if Apples were removed in January, then I do not want them to show on the list in April.