Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Trouble with rolling average

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 m...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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]
    ))