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



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

7 Replies

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



    • Anonymous's avatar
      Anonymous
      Not applicable
      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

        It works, thank You so much! 🙂