Trouble with rolling average

[Anonymous]

Hi All! 
I try to add a measure with rolling average calculation. Expected result should be as follow:
-in the first week of a given year return the value of the measure [Site CTS week]
-at next month calculate rolling average for example for 1/16/2023: (62.71%+44.39%+64.31%)/3= 57.14%

Something is wrong at my calculation, because instead of getting 57,14% at 1/16/2023 I recive 54,03%:

I will be grateful when someone could help me 🙂
This is my measure definitions:

Site CTS week rolling average =

VAR CurrentYear = MAX(WeeklyTable[year])

VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])

VAR StartDate = CALCULATE(

    MIN(WeeklyTable[ConvertedDate]),

    FILTER(ALL(WeeklyTable),

     WeeklyTable[year] = CurrentYear))

var weeksInRange=

FILTER(

ALL(WeeklyTable),

WeeklyTable[ConvertedDate] >=StartDate &&

WeeklyTable[ConvertedDate] <=CurrentWeek &&

WeeklyTable[year]=CurrentYear)

RETURN

IF(

    CurrentWeek = StartDate,

    [Site CTS week],

  AVERAGEX(weeksInRange, [Site CTS week]

    )

)

Site CTS week = AVERAGEX(SUMMARIZE(WeeklyTable, WeeklyTable[resource_id],WeeklyTable[cal_cd], "SCTS", AVERAGE(WeeklyTable[CTS])), [SCTS])

[Anonymous]

Sorry for mistake 🙂 I would like to achive something simillar, but calculate comulative avg starts from beinning af the year and finish at the last date of year. I changed Yours dax, but still doesn't work correctly 😕

var CurrentYear = MAX(WeeklyTable[year])

VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])

VAR StartDate = CALCULATE(

    MIN(WeeklyTable[ConvertedDate]),

    FILTER(ALL(WeeklyTable),

     WeeklyTable[year] = CurrentYear))

VAR WeeksInRange =

    FILTER(

        ALL(WeeklyTable),

        WeeklyTable[ConvertedDate] <= CurrentWeek && WeeklyTable[year] = CurrentYear

    )

RETURN

IF( CurrentWeek = StartDate,

    [Site CTS week],

AVERAGEX(

    SUMMARIZE(WeeksInRange, WeeklyTable[ConvertedDate], "AverageCTS", [Site CTS week]),

    [AverageCTS]

))

[Anonymous · Accepted solution]

Hi Bibiano_Geraldo ,thanks for the quick reply, I'll add more.

Hi @Anonymous ,

Regarding your question, I think you can create a virtual table to store the results you get in your visual. Finally, use this virtual table to find the average value you need

Something like this 

Measure = 
VAR _table = SUMMARIZE(ALL('Table'),[ConvertedDate],"Result",[Site CTS Week])
VAR _CurrentWeek = SELECTEDVALUE('Table'[ConvertedDate])
RETURN AVERAGEX(FILTER(_table,'Table'[ConvertedDate] <= _CurrentWeek),[Result])

Best Regards,
Wenbin Zhou

[Anonymous]

It works, thank You so much! 🙂

[Bibiano_Geraldo]

Hi, Please try the following DAX:

1. RollingWindowStartDate: This calculates the start of your rolling window (e.g., 3 weeks before the current date). Adjust this value based on how many weeks you want in your rolling average.
2. weeksInRange Filter: The filter is now based on RollingWindowStartDate to CurrentWeek, limiting it to the desired number of weeks.

Site CTS week rolling average =
VAR CurrentYear = MAX(WeeklyTable[year])
VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])
VAR StartDate = CALCULATE(
    MIN(WeeklyTable[ConvertedDate]),
    FILTER(ALL(WeeklyTable),
     WeeklyTable[year] = CurrentYear))

-- Define the range of weeks for rolling average (adjust n to your desired window, e.g., 3 weeks)
VAR RollingWindowStartDate = CALCULATE(
    MAX(WeeklyTable[ConvertedDate]),
    FILTER(
        ALL(WeeklyTable),
        WeeklyTable[ConvertedDate] <= CurrentWeek &&
        WeeklyTable[ConvertedDate] >= CurrentWeek - 21 -- 3 weeks back (7 days * 3)
    )
)

VAR weeksInRange = 
    FILTER(
        ALL(WeeklyTable),
        WeeklyTable[ConvertedDate] >= RollingWindowStartDate &&
        WeeklyTable[ConvertedDate] <= CurrentWeek &&
        WeeklyTable[year] = CurrentYear
    )

RETURN
IF(
    CurrentWeek = StartDate,
    [Site CTS week], -- Use the measure for the first week
    AVERAGEX(weeksInRange, [Site CTS week]) -- Calculate average for the range
)

[Anonymous]

Unfortunatelly it doesn't work. Maybe I specify: I would like to achieve rolling weekly average as following:
-in the first week of a given year return the value of the measure [Site CTS week]
-in the second week average from the first two week of given year (62.71+44.39)/2= 53.55%
 -in the third week average from the first three week of given year (62.71+44.39+64.31)/3= 57,14%
-analogously from next weeks until the end of given year.

I try to change Your measure as following, but it also dosn't work:

VAR CurrentYear = MAX(WeeklyTable[year])

VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])

VAR StartDate = CALCULATE(

    MIN(WeeklyTable[ConvertedDate]),

    FILTER(ALL(WeeklyTable),

     WeeklyTable[year] = CurrentYear))

-- Define the range of weeks for rolling average (adjust n to your desired window, e.g., 3 weeks)

VAR RollingWindowStartDate = CALCULATE(

    MAX(WeeklyTable[ConvertedDate]),

    FILTER(

        ALL(WeeklyTable),

    //     WeeklyTable[ConvertedDate] <= CurrentWeek &&

    //   WeeklyTable[ConvertedDate] >= CurrentWeek - 21 -- 3 weeks back (7 days * 3)

   WeeklyTable[ConvertedDate]>= StartDate &&  WeeklyTable[ConvertedDate] <= CurrentWeek

    )

)

VAR weeksInRange =

    FILTER(

        ALL(WeeklyTable),

        WeeklyTable[ConvertedDate] >= RollingWindowStartDate &&

        WeeklyTable[ConvertedDate] <= CurrentWeek &&

        WeeklyTable[year] = CurrentYear

    )

RETURN

IF(

    CurrentWeek = StartDate,

    [Site CTS week], -- Use the measure for the first week

    AVERAGEX(weeksInRange, [Site CTS week]) -- Calculate average for the range

[Bibiano_Geraldo]

Great, its Commulative average, please try the following Dax:

Site CTS week rolling average =
VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])

VAR WeeksInRange =
    FILTER(
        ALL(WeeklyTable),
        WeeklyTable[ConvertedDate] <= CurrentWeek
    )

RETURN
AVERAGEX(
    SUMMARIZE(WeeksInRange, WeeklyTable[ConvertedDate], "AverageCTS", [Site CTS week]),
    [AverageCTS]
)

[Anonymous]

Sorry for mistake 

 I would like to achive something simillar, but calculate comulative avg starts from beinning af the year and finish at the last date of year. I changed Yours dax, but still doesn't work correctly 

var CurrentYear = MAX(WeeklyTable[year])

VAR CurrentWeek = MAX(WeeklyTable[ConvertedDate])

VAR StartDate = CALCULATE(

    MIN(WeeklyTable[ConvertedDate]),

    FILTER(ALL(WeeklyTable),

     WeeklyTable[year] = CurrentYear))

VAR WeeksInRange =

    FILTER(

        ALL(WeeklyTable),

        WeeklyTable[ConvertedDate] <= CurrentWeek && WeeklyTable[year] = CurrentYear

    )

RETURN

IF( CurrentWeek = StartDate,

    [Site CTS week],

AVERAGEX(

    SUMMARIZE(WeeksInRange, WeeklyTable[ConvertedDate], "AverageCTS", [Site CTS week]),

    [AverageCTS]

))