Applying filter based on a subset of data

I am interested to apply a filter based on a subset of data.

Let's take an example:

This is the superstore data set example:

In the above view, we can see that it shows the data for a few specific states and corresponding profit and earnings in 2021.

What I am aiming to do is that I want to say filter this list based on L7 days profit.

So, say something like, I want the sum of profit in the last 7 days in each state should be more than $500

I've tried this formula:

Sum( date >= today()-7 and date<= today() then profit else end)

This works, however, it changes the entire to view to just last 7 days (L7) however I want the rest of the data also - entire 2021.

There are many reasons why I want to do this, see the trend where a state was making money and in last 7 days it stopped etc.

Can anyone help? Thank you!