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

    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.

     

     

  • 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.