Moving Average

[Friso]

Hello, 

To calculate the Moving Average Total of different WEEKS i need a liitle bit help. For example I have the results of three years and each week give a result.. I have made a INDEZ table and each combination of a Year - Week has his own number. For example 2016 week 1 = 1, 2016 week 2 = 2, but 2017 week 1 = 53, etc......Is is possible to calculate the MAT using this Indexnumer? 

For example in this week (2018 Week 13 = Index number 117)  I will calculate the results from Index number 67 til 117 (total 52 weeks...) 

Who can help me? 

Friso

[v-yulgu-msft · Accepted solution]

Hi @Friso,

Suppose there existing a relationship between data table and INDEZ table based on Year-Week column. Please try to add calculated columns to your data table.

Index No = RELATED(INDEZ[Index])

Moving Average = CALCULATE(AVERAGE(Test1[Sales]),FILTER(Test1,Test1[Index No]<=EARLIER(Test1[Index No])&&Test1[Index No]>=(EARLIER(Test1[Index No])-50)))

Best regards,

Yuliana Gu