Moving average - weekly

[Noki]

Hi! Data on receivables arrive on a specific day of the week. How can I create a variable weekly moving average?

Thx

[amitchandak]

@Noki , if you need across week

Have week rank in your date or week table, prefer a separate table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measure

Last 2 weeks = CALCULATE(AverageX(values('Date'[Week Rank]), calculate(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

If you data at date level then  , Avg Above day level

AverageX(values('Date'[Date]), calculate(sum('Table'[Qty])))

[Anonymous]

Hi @Noki,

You can write a measure expression and use a variable to store the current date, then you can use the year and weeknum function with date values as conditions to filter and calculate the rolling average based on the week.

formula =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
                && [Date] <= currdate
        ),
        VALUES ( Table[Category] )
    )

Regards,

Xiaoxin Sheng

[Noki]

Where can I enter how many weekly averages to calculate?

[Anonymous]

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