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

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