Forum Discussion

Noki's avatar
Noki
Regular Visitor
4 years ago

Moving average - weekly

Hi! Data on receivables arrive on a specific day of the week. How can I create a variable weekly moving average? Thx
Noki's avatar
Noki
Regular Visitor
4 years ago

Where can I enter how many weekly averages to calculate?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI Noki,

My formula is calculated on the current week rolling daily average, if you mean calculating across multiple weeks, you can try to use the following formulas.

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR currCategory =
    SELECTEDVALUE ( Table[Category] )
VAR weekRange = 2
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 * weekRange )
VAR filtered =
    FILTER ( Table, [Date] >= prevDate && [Category] = currCategory )
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            filtered,
            "Year", YEAR ( Table[Date] ),
            "Week Number", WEEKNUM ( Table[Date], 2 )
        ),
        [Year],
        [Week Number],
        "WeeklySales", SUM ( Table[Sales] )
    )
RETURN
    AVERAGEX ( summary, [WeeklySales] )

Regards,

Xiaoxin Sheng