Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
alexpetersen
Frequent Visitor

Moving Average by Ranked Index

‎03-13-2023 12:26 PM

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. 

My Average.PNG

 

 

 

 

 

 

Greatly appreciate any and all help with this issue.

Thank you in advance!

Labels:
Message 1 of 11
875 Views
0
1 ACCEPTED SOLUTION
BrianConnelly
Resolver III
Resolver III
In response to alexpetersen

‎03-14-2023 07:36 AM

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

BrianConnelly_0-1678805133941.png

 

 

View solution in original post

Message 9 of 11
744 Views
10 REPLIES 10
alexpetersen
Frequent Visitor

‎03-13-2023 01:35 PM

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

 

Data Set.PNG

Message 5 of 11
801 Views
0
alexpetersen
Frequent Visitor
In response to alexpetersen

‎03-13-2023 02:25 PM

https://matthewsretailgroup-my.sharepoint.com/:u:/g/personal/alexander_petersen_matthews_com/Efnutus... 

 

Does this link work for you to get to the Power BI file?

 

Message 6 of 11
789 Views
0
grantsamborn
Solution Sage
Solution Sage
In response to alexpetersen

‎03-13-2023 03:05 PM

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

 

Message 7 of 11
777 Views
0
alexpetersen
Frequent Visitor
In response to grantsamborn

‎03-14-2023 06:32 AM

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

My Average.PNG

 

 

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

 

 

Proposals Presented.png

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

 

Message 8 of 11
750 Views
0
BrianConnelly
Resolver III
Resolver III
In response to alexpetersen

‎03-14-2023 07:36 AM

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

BrianConnelly_0-1678805133941.png

 

 

Message 9 of 11
745 Views
alexpetersen
Frequent Visitor
In response to BrianConnelly

‎03-14-2023 07:46 AM

Thank you! 

 

Would you mind posting your power bi file or picture of your data model for me to replicate. I am having issues getting it to work on my end?

Message 10 of 11
741 Views
0
BrianConnelly
Resolver III
Resolver III
In response to alexpetersen

‎03-14-2023 07:48 AM

I didn't use a data model, just made a copy of your posted data.

BrianConnelly_0-1678805267498.png

 

The measures are posted in previous.  If you have a date table with a relationship then switch out the date fields in the measure with the date fields of the date table.

 

BrianConnelly_1-1678805383227.png

 

Also, check your data types, i.e. Date fields are of data type Date.  Numbers are of data type Whole or Decimal, etc.

 

Message 11 of 11
738 Views
BrianConnelly
Resolver III
Resolver III

‎03-13-2023 12:32 PM

Change MyIndex to 

VAR MyIndex = SELECTEDVALUE('Proposals'[Index],<A Default Value>)

 

A default value can be something like MAX()

MAX('Proposals'[Index])

 

Change your filter to

 

FILTER(ALL('Proposals')....

 

Message 2 of 11
870 Views
0
alexpetersen
Frequent Visitor
In response to BrianConnelly

‎03-13-2023 12:42 PM

Thank you so much for your quick response. 

 

I have updated the code to the following:

 

Moving Average = 
VAR MyIndex = SELECTEDVALUE('Proposals'[Index],MAX('Proposals'[Index]))
VAR myResult = 
    AVERAGEX(
        FILTER(ALL( 'Proposals'),
            'Proposals'[Index] > MyIndex-13  && 
            'Proposals'[Index] <= MyIndex
           ),'Proposals'[Proposals]
           )
RETURN FIXED(myResult,2)

 

Unfortunately, the moving average column is all coming back as "0.00"

 

Is there an easy way to share my Power BI file?

 

Message 3 of 11
860 Views
0
BrianConnelly
Resolver III
Resolver III
In response to alexpetersen

‎03-13-2023 01:20 PM

You have to upload to GIT or Onedrive and share Publicly the link.  You could also past sample data and image of the dataset.

Message 4 of 11
835 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

View All