Forum Discussion

Tihannah's avatar
Tihannah
Resolver II
4 years ago
Solved

Filtering based on Date Selection

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...
Need Help
  • Tihannah's avatar
    Tihannah
    4 years ago

    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's avatar
danextian
Super User
4 years ago

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's avatar
Tihannah
Resolver II
4 years ago

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.