Forum Discussion
4 Week Weekly Rolling Average for specified Years
Hi, I'm trying to create 4 week, weekly rolling averages using ISO Weeknumber for the current year and 2019. Previously I've used the SAMEPERIODLASTYEAR for all my prior years, but I've noticed t...
Update, after lots and lots of trawling, I've eventually come across a formula that works for both years
Bookings 2023 4 Week Weekly Rolling Average =
VAR RelevantWeeks =
CALCULATETABLE(
VALUES(Dates[ISO Weeknumber]),
DATESBETWEEN(
Dates[Date],
MAX(Dates[Date]) - ((7*4)-1),
MAX(Dates[Date])
),
REMOVEFILTERS(Dates)
)
RETURN
AVERAGEX(
RelevantWeeks,
[Bookings 2023]
)
Bookings 2019 4 Week Weekly Rolling Average =
VAR RelevantWeeks =
CALCULATETABLE(
VALUES(Dates[ISO Weeknumber]),
DATESBETWEEN(
Dates[Date],
MAX(Dates[Date]) - 27,
MAX(Dates[Date])
),
REMOVEFILTERS(Dates)
)
RETURN
AVERAGEX(
RelevantWeeks,
[Bookings 2019]
)
I do have a follow-up question, is there any way using the ISO Week number from my date table, that I could get the rolling average to be continuous because you'll notice that the first 3 weeks are rolling 1, 2 & 3 weeks respectively, how could I get the final weeks of 2022 & 2018 into this data? This isn't so much of an issue with the 4-week rolling average, as this period passes quickly, but I also need a 13-week rolling average, where it does look more problematic.
The end goal is to have the 2023 bookings by ISO WeekNumber calculated as a % of 2019 bookings on a rolling 4 and 13-week average, but to factor in the final few weeks from the years before. I realize by hardcoding in 2019/2023 into my bookings calculation that I've limited myself here, but I'm not sure of the way around this. Thanks