cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

30 60 90 day filter | how to remove old dates

Hello all. I am trying to create a 30,60,90 day filter for a table that has tasks, and then states their due date. Except the DAX formula keeps old dates, meaning dates that are older than today, as seen in the picture below. 

 

What did I need to add into my formula to remove the October 19, 2020 Due Date? 

 

The end result should be that when I select 30 days pending only one task appears, the November 1, 2020 task.

 

Days Pending = CALCULATE(MAX('Table'[Task Next Milestone Due Date]),
FILTER('Table','Table'[Task Next Milestone Due Date]<=TODAY()+SELECTEDVALUE('Days Pending'[Days Pending],MAX('Days Pending'[Days Pending]))))

 

beingandbrian_0-1603728436154.png

 

 

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @beingandbrian ,

 

You are only defining the top values for your filtering so in term of formula you are returning all the values from your table where the Milestone Due Date  is lower than TODAY + X days so all your values are within this values, you need to place also the lower value.

 

Rewrite your measure to :

 

Days Pending =
CALCULATE (
    MAX ( 'Table'[Task Next Milestone Due Date] ),
    FILTER (
        'Table',
        'Table'[Task Next Milestone Due Date]
            <= TODAY ()
                + SELECTEDVALUE (
                    'Days Pending'[Days Pending],
                    MAX ( 'Days Pending'[Days Pending] )
                )
            && 'Table'[Task Next Milestone Due Date] >= TODAY ()
    )
)

 

The formula after the && is returning all the values where the due date is higher than today, so now you have the following syntax:

 

Today <= Milestone Due Date <= Today + Xdays

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

1 REPLY 1
Super User III
Super User III

Hi @beingandbrian ,

 

You are only defining the top values for your filtering so in term of formula you are returning all the values from your table where the Milestone Due Date  is lower than TODAY + X days so all your values are within this values, you need to place also the lower value.

 

Rewrite your measure to :

 

Days Pending =
CALCULATE (
    MAX ( 'Table'[Task Next Milestone Due Date] ),
    FILTER (
        'Table',
        'Table'[Task Next Milestone Due Date]
            <= TODAY ()
                + SELECTEDVALUE (
                    'Days Pending'[Days Pending],
                    MAX ( 'Days Pending'[Days Pending] )
                )
            && 'Table'[Task Next Milestone Due Date] >= TODAY ()
    )
)

 

The formula after the && is returning all the values where the due date is higher than today, so now you have the following syntax:

 

Today <= Milestone Due Date <= Today + Xdays

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors