Date filtering and moving average calculation

[firescape]

I'm using your run of the mill moving average DAX formula and noticed that filtered dates do not get excluded from the calculation

7 Day Avg = 
CALCULATE(AVERAGEX (
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date] ),
        -7,
        DAY
    ),
    [Staff #]
))

I'm assuming the is due to DATESINPERIOD not respecting filters, is there any way to fix that?

Filtering on any other column works as expected.

[AntrikshSharma]

@firescape I didn't get the first few words of the first sentence, you should try to write you code like this:

7 Day Avg =
CALCULATE (
    AVERAGEX ( VALUES ( 'Calendar'[Date] ), [Staff #] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -7, DAY )
)

I have also made a video on moving average, in case if you are interested, then check the first link after my signature.

[firescape]

I tried that formula and I'm still having the same issue, see below for an example.

Before filtering:

After filtering out January 11th notice how the average hasn't chaged for January 12th when it should be 175.