Forum Discussion

firescape's avatar
firescape
Frequent Visitor
5 years ago

Date filtering and moving average calculation

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 ( '...
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    firescape
    Frequent Visitor
    5 years ago

     

    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.