8-Week moving Average not working

[egrospe17]

Hello PBI Experts,

I've been scrambling my brains on trying to figure out what is wrong with my DAX moving average formula. Basically, my dataset consists of a date (start of week) and Paid invoiced amount. I'm using the following DAX formula to calculate the 8-week moving average: 

8W_Moving_Avg = 
VAR CurrentDate = MAX('New Calendar'[Date])
VAR StartDate = CurrentDate - 56 -- 12 weeks * 7 days

RETURN
    CALCULATE(
        AVERAGEX(
            SUMMARIZE(
                FILTER(
                    ALL('New Calendar'),
                    'New Calendar'[Date] >= StartDate &&
                    'New Calendar'[Date] <= CurrentDate
                ),
                'New Calendar'[Week from Start],
                "TotalValue", CALCULATE(SUM('WeeklyInvoicePaid'[InvoicePaidUSD]))
            ),
            [TotalValue]
        )
    )

However when I try to validate the numbers in excel, it looks like the DAX formula is incorrect. What am i doing wrong? 

[OwenAuger · Accepted solution]

Hi @egrospe17 

It appears that the 8W_Moving_Avg measure is returning a 9-week moving average since it applies a date filter covering a range of 57 days, which extends into a 9th week.

I would suggest rewriting below this using DATESINPERIOD. The 3rd argument of DATESINPERIOD specifies the number of intervals (days in this case) beginning from the date provided as the 2nd argument (StartDate). Negative values for NumberOfIntervals produce periods extending backwards in time starting from StartDate.

Also, I'm assuming that 'New Calendar' is marked as a date table with the Date column being 'New Calendar'[Date], so you don't need to include  ALL ( 'New Calendar' ) or REMOVEFILTERS ( 'New Calendar' ).

8W_Moving_Avg =
VAR CurrentDate =
    MAX ( 'New Calendar'[Date] )
VAR NumDays = 56 -- 8 weeks
RETURN
    CALCULATE (
        AVERAGEX (
            VALUES ( 'New Calendar'[Week from Start] ),
            CALCULATE ( SUM ( WeeklyInvoicePaid[InvoicePaidUSD] ) )
        ),
        DATESINPERIOD ( 'New Calendar'[Date], CurrentDate, - NumDays, DAY )
    )

Alternatively, you could change 56 to 55 in your original measure, but this suggested measure should be more efficient as it filters the Date column rather than the 'New Calendar' table (see here). Also, SUMMARIZE shouldn't be used to add columns in general (see here).

Does this work for you?

[egrospe17]

Thank SO MUCH! This solved the issue.