Forum Discussion

Frequent Visitor

3 years ago

Solved

Moving Average by Ranked Index

Dear Community, I am trying to calculate the 13 Week Moving Average. My data model is very simple. Please see below for my current attempt and error message. G...

3 years ago

Here is your code...

90 Day Average = 
VAR sDate = MAX('Table2'[Date])
VAR bDate = sDate-90
VAR sValue = CALCULATE(AVERAGE('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate))
RETURN sValue

Change the "Calculate(Average" to a Max or Min for the other two measures and get the MAX Date.

90 Min = 
VAR sDate = MAXX(ALL('Table2'[Date]),'Table2'[Date])
VAR bDate = sDate-90
VAR sValue = CALCULATE(MIN('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate))
RETURN sValue

Frequent Visitor

3 years ago

Please see below for sample data:

Date Proposals

1/3/2021	56
1/10/2021	101
1/17/2021	97
1/24/2021	145
1/31/2021	205
2/7/2021	125
2/14/2021	123
2/21/2021	143
2/28/2021	156
3/7/2021	127
3/14/2021	141
3/21/2021	132
3/28/2021	135
4/4/2021	162
4/11/2021	175
4/18/2021	167
4/25/2021	209
5/2/2021	182
5/9/2021	205
5/16/2021	154
5/23/2021	218
5/30/2021	158
6/6/2021	248
6/13/2021	140
6/20/2021	145
6/27/2021	112
7/4/2021	107
7/11/2021	168
7/18/2021	156
7/25/2021	146
8/1/2021	163
8/8/2021	161
8/15/2021	163
8/22/2021	196
8/29/2021	193
9/5/2021	156
9/12/2021	217
9/19/2021	262
9/26/2021	243
10/3/2021	217
10/10/2021	232
10/17/2021	267
10/24/2021	246
10/31/2021	191
11/7/2021	332
11/14/2021	262
11/21/2021	70
11/28/2021	197
12/5/2021	87
12/12/2021	73
12/19/2021	21
12/26/2021	7
1/2/2022	111
1/9/2022	219
1/16/2022	219
1/23/2022	210
1/30/2022	293
2/6/2022	261
2/13/2022	212
2/20/2022	148
2/27/2022	203
3/6/2022	225
3/13/2022	201
3/20/2022	184
3/27/2022	199
4/3/2022	155
4/10/2022	200
4/17/2022	235
4/24/2022	213
5/1/2022	286
5/8/2022	135
5/15/2022	198
5/22/2022	106
5/29/2022	134
6/5/2022	170
6/12/2022	221
6/19/2022	163
6/26/2022	121
7/3/2022	124
7/10/2022	149
7/17/2022	164
7/24/2022	136
7/31/2022	213
8/7/2022	209
8/14/2022	186
8/21/2022	208
8/28/2022	173
9/4/2022	141
9/11/2022	221
9/18/2022	266
9/25/2022	216
10/2/2022	279
10/9/2022	243
10/16/2022	240
10/23/2022	216
10/30/2022	230
11/6/2022	238
11/13/2022	287
11/20/2022	55
11/27/2022	285
12/4/2022	290
12/11/2022	141
12/18/2022	31
12/25/2022	12
1/2/2023	75
1/9/2023	116
1/16/2023	167
1/23/2023	196
1/30/2023	208
2/6/2023	183
2/13/2023	271
2/20/2023	196
2/27/2023	195