Forum Discussion

alexpetersen's avatar
alexpetersen
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...
Need Help
  • BrianConnelly's avatar
    BrianConnelly
    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

     

     

alexpetersen's avatar
alexpetersen
Frequent Visitor
3 years ago

Please see below for sample data:

 

 

Date                 Proposals

1/3/202156
1/10/2021101
1/17/202197
1/24/2021145
1/31/2021205
2/7/2021125
2/14/2021123
2/21/2021143
2/28/2021156
3/7/2021127
3/14/2021141
3/21/2021132
3/28/2021135
4/4/2021162
4/11/2021175
4/18/2021167
4/25/2021209
5/2/2021182
5/9/2021205
5/16/2021154
5/23/2021218
5/30/2021158
6/6/2021248
6/13/2021140
6/20/2021145
6/27/2021112
7/4/2021107
7/11/2021168
7/18/2021156
7/25/2021146
8/1/2021163
8/8/2021161
8/15/2021163
8/22/2021196
8/29/2021193
9/5/2021156
9/12/2021217
9/19/2021262
9/26/2021243
10/3/2021217
10/10/2021232
10/17/2021267
10/24/2021246
10/31/2021191
11/7/2021332
11/14/2021262
11/21/202170
11/28/2021197
12/5/202187
12/12/202173
12/19/202121
12/26/20217
1/2/2022111
1/9/2022219
1/16/2022219
1/23/2022210
1/30/2022293
2/6/2022261
2/13/2022212
2/20/2022148
2/27/2022203
3/6/2022225
3/13/2022201
3/20/2022184
3/27/2022199
4/3/2022155
4/10/2022200
4/17/2022235
4/24/2022213
5/1/2022286
5/8/2022135
5/15/2022198
5/22/2022106
5/29/2022134
6/5/2022170
6/12/2022221
6/19/2022163
6/26/2022121
7/3/2022124
7/10/2022149
7/17/2022164
7/24/2022136
7/31/2022213
8/7/2022209
8/14/2022186
8/21/2022208
8/28/2022173
9/4/2022141
9/11/2022221
9/18/2022266
9/25/2022216
10/2/2022279
10/9/2022243
10/16/2022240
10/23/2022216
10/30/2022230
11/6/2022238
11/13/2022287
11/20/202255
11/27/2022285
12/4/2022290
12/11/2022141
12/18/202231
12/25/202212
1/2/202375
1/9/2023116
1/16/2023167
1/23/2023196
1/30/2023208
2/6/2023183
2/13/2023271
2/20/2023196
2/27/2023195

 

    • grantsamborn's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Hi alexpetersen 

      Would something like this help?

       

      My RA = 
VAR _Curr = SELECTEDVALUE( 'Proposals'[Index] )
VAR _Result =
    AVERAGEX(
        FILTER(
            ALL( 'Proposals' ),
            'Proposals'[Index] <= _Curr
                && 'Proposals'[Index] >= _Curr - 12
        ),        
        'Proposals'[Proposals]
    )
RETURN
    _Result

       

      • alexpetersen's avatar
        alexpetersen
        Frequent Visitor
        3 years ago

        Hello grantsamborn 

         

        Thank you for your help!

        This has helped, but I am still having issues with the formula trying to work on each individual date instead of the once a week. (1/3/21, 1/10/21, 1/17/21...)

         

         

        I am trying to recreate this excel file, I hope this helps. 

         

        Date	Presented	 90 Day Rolling Average 	Max	Min
	Count of Proposal: Proposal Name			
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	 175.85 	 235.85 	 156.15 
6/20/2021	145	 175.77 	 235.85 	 156.15 
6/27/2021	112	 176.77 	 235.85 	 156.15 
7/4/2021	107	 175.00 	 235.85 	 156.15 
7/11/2021	168	 170.77 	 235.85 	 156.15 
7/18/2021	156	 170.23 	 235.85 	 156.15 
7/25/2021	146	 169.38 	 235.85 	 156.15 
8/1/2021	163	 164.54 	 235.85 	 156.15 
8/8/2021	161	 163.08 	 235.85 	 156.15 
8/15/2021	163	 159.69 	 235.85 	 156.15 
8/22/2021	196	 160.38 	 235.85 	 156.15 
8/29/2021	193	 158.69 	 235.85 	 156.15 
9/5/2021	156	 161.38 	 235.85 	 156.15 
9/12/2021	217	 154.31 	 235.85 	 156.15 
9/19/2021	262	 160.23 	 235.85 	 156.15 
9/26/2021	243	 169.23 	 235.85 	 156.15 
10/3/2021	217	 179.31 	 235.85 	 156.15 
10/10/2021	232	 187.77 	 235.85 	 156.15 
10/17/2021	267	 192.69 	 235.85 	 156.15 
10/24/2021	246	 201.23 	 235.85 	 156.15 
10/31/2021	191	 208.92 	 235.85 	 156.15 
11/7/2021	332	 211.08 	 235.85 	 156.15 
11/14/2021	262	 224.23 	 235.85 	 156.15 
11/21/2021	70	 231.85 	 235.85 	 156.15 
11/28/2021	197	 222.15 	 235.85 	 156.15 
12/5/2021	87	 222.46 	 235.85 	 156.15 
12/12/2021	73	 217.15 	 235.85 	 156.15 
12/19/2021	21	 206.08 	 235.85 	 156.15 
12/26/2021	7	 187.54 	 235.85 	 156.15 
1/2/2022	111	 169.38 	 235.85 	 156.15 
1/9/2022	219	 161.23 	 235.85 	 156.15 
1/16/2022	219	 160.23 	 235.85 	 156.15 
1/23/2022	210	 156.54 	 235.85 	 156.15 
1/30/2022	293	 153.77 	 235.85 	 156.15 
2/6/2022	261	 161.62 	 235.85 	 156.15 
2/13/2022	212	 156.15 	 235.85 	 156.15 
2/20/2022	148	 152.31 	 235.85 	 156.15 
2/27/2022	203	 158.31 	 235.85 	 156.15 
3/6/2022	225	 158.77 	 235.85 	 156.15 
3/13/2022	201	 169.38 	 235.85 	 156.15 
3/20/2022	184	 179.23 	 235.85 	 156.15 
3/27/2022	199	 191.77 	 235.85 	 156.15 
4/3/2022	155	 206.54 	 235.85 	 156.15 
4/10/2022	200	 209.92 	 235.85 	 156.15 
4/17/2022	235	 208.46 	 235.85 	 156.15 
4/24/2022	213	 209.69 	 235.85 	 156.15 
5/1/2022	286	 209.92 	 235.85 	 156.15 
5/8/2022	135	 209.38 	 235.85 	 156.15 
5/15/2022	198	 199.69 	 235.85 	 156.15 
5/22/2022	106	 198.62 	 235.85 	 156.15 
5/29/2022	134	 195.38 	 235.85 	 156.15 
6/5/2022	170	 190.08 	 235.85 	 156.15 
6/12/2022	221	 185.85 	 235.85 	 156.15 
6/19/2022	163	 187.38 	 235.85 	 156.15 
6/26/2022	121	 185.77 	 235.85 	 156.15 
7/3/2022	124	 179.77 	 235.85 	 156.15 
7/10/2022	149	 177.38 	 235.85 	 156.15 
7/17/2022	164	 173.46 	 235.85 	 156.15 
7/24/2022	136	 168.00 	 235.85 	 156.15 
7/31/2022	213	 162.08 	 235.85 	 156.15 
8/7/2022	209	 156.46 	 235.85 	 156.15 
8/14/2022	186	 162.15 	 235.85 	 156.15 
8/21/2022	208	 161.23 	 235.85 	 156.15 
8/28/2022	173	 169.08 	 235.85 	 156.15 
9/4/2022	141	 172.08 	 235.85 	 156.15 
9/11/2022	221	 169.85 	 235.85 	 156.15 
9/18/2022	266	 169.85 	 235.85 	 156.15 
9/25/2022	216	 177.77 	 235.85 	 156.15 
10/2/2022	279	 185.08 	 235.85 	 156.15 
10/9/2022	243	 197.00 	 235.85 	 156.15 
10/16/2022	240	 204.23 	 235.85 	 156.15 
10/23/2022	216	 210.08 	 235.85 	 156.15 
10/30/2022	230	 216.23 	 235.85 	 156.15 
11/6/2022	238	 217.54 	 235.85 	 156.15 
11/13/2022	287	 219.77 	 235.85 	 156.15 
11/20/2022	55	 227.54 	 235.85 	 156.15 
11/27/2022	285	 215.77 	 235.85 	 156.15 
12/4/2022	290	 224.38 	 235.85 	 156.15 
12/11/2022	141	 235.85 	 235.85 	 156.15 
12/18/2022	31	 211.62 	 235.85 	 156.15 
12/25/2022	12	 195.92 	 235.85 	 156.15 
1/2/2023	75	 180.23 	 235.85 	 156.15 
1/9/2023	116	 170.46 	 235.85 	 156.15 
1/16/2023	167	 164.85 	 235.85 	 156.15 
1/23/2023	196	 163.31 	 235.85 	 156.15 
1/30/2023	208	 161.62 	 235.85 	 156.15 
2/6/2023	183	 157.38 	 235.85 	 156.15 
2/13/2023	271	 156.15 	 235.85 	 156.15 
2/20/2023	196	 167.00 	 235.85 	 156.15 
2/27/2023	195	 160.08 	 235.85 	 156.15

         

         

        Thank you again for your help! Have a great day!