Forum Discussion

LaurenceSD's avatar
LaurenceSD
Icon for Advocate II rankAdvocate II
3 years ago

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 that the numbers don't aligh when comparing to the ISO weeks and the request has come in to use ISO weeks.

 

I've these calculations

Bookings = COUNTROWS(PBI_BookingsSales)
 
Bookings 2023 = CALCULATE([Bookings],FILTER(Dates,Dates[Year]=2023))
 
Bookings 2019 = CALCULATE([Bookings],FILTER(Dates,Dates[Year]=2019))
 
CountWeeks4 = countrows(DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -4, Day ))
 
Bookings 2023 4 Week Weekly Rolling Average =
DIVIDE(CALCULATE (
[Bookings 2023],
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -(7*4), DAY )),[CountWeeks4])
 
Bookings 2019 4 Week Weekly Rolling Average =
DIVIDE(CALCULATE (
[Bookings 2019],
DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -(7*4), DAY )),[CountWeeks4])
 
These rolling averages have worked fine when I've been using them with Bookings measures that had SAMEPERIODLASTYEAR in them instead of a hardcoded year. 
 
The measure seems to work fine for 2023 but not for 2019, it's just returning blanks
 

Can anyone suggest what I am doing wrong? I ultimately want to see the rolling 4 week (and then 13 week) for 2023 as a % of the 2019 equivalents, hence me needing measures for each, rather than using year as a legend

 

Thanks

 

Laurence

 
Need Help

1 Reply

  • LaurenceSD's avatar
    LaurenceSD
    Icon for Advocate II rankAdvocate II
    3 years ago

    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