Forum Discussion
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
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
Where can I enter how many weekly averages to calculate?
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