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

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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