Moving Range calculation for I-MR control chart

[peter789]

As the next step of my control chart project I realise I need to calculate the average of the Moving Range of a group of values in my Pivot Table.
Wikipedia defines the calculation formula here:

https://en.wikipedia.org/wiki/Shewhart_individuals_control_chart

As a first step I have created an Index column in my data using Power Query. Next I tried to test the use of EARLIER to find the minimum of one value and its neighbour in the next row. However this is where it gets stuck.
Any help with the measure I am using would be appreciated please or advice as to whether I should be taking a different tack. I have been reluctant to try and create calculated columns in my data source as just even adding the index column has increased the file size by 20%.
The measure I've tried is:

=CALCULATE(MIN(QryAnalysis[Value]),FILTER(QryAnalysis,QryAnalysis[Oxide]=EARLIER(QryAnalysis[Oxide]) && QryAnalysis[Index]=EARLIER((QryAnalysis[Index]) && QryAnalysis[Index]=EARLIER(QryAnalysis[Index])+1)))

The error is:
"This formula is invalid or incomplete: 'Calculation error in measure 'QryAnalysis'[Minimum of two values]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'. "

My Pivot Table looks like:

		Oxide
		Na2O %
		Average Value
Sample Date	Index	Amber
03/04/2017	132950	11.97
10/04/2017	132951	12.00
18/04/2017	132952	11.96
24/04/2017	132953	12.09
02/05/2017	132954	12.11
08/05/2017	132955	12.07
15/05/2017	132956	12.08
22/05/2017	132957	12.08
29/05/2017	132958	11.98

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Should I plough on? My next measure would be to work out the Max, then AVERAGEX MAX- MIN.

 

[gazpage]

Average Moving Range Selected=
 VAR
varMR=[Max of last two]-[Min of last two] 

RETURN
 IF(ISBLANK( [Moving Range] ),
    BLANK(),
    SUMX(
        ALLSELECTED ( QryAnalysis[Sample Date] ),
        varMR
    )
)

 

[peter789]

Unfortunately:

Sample Date	Colour Group	Average Value	Average Selected values	Starting Index	Min of last two	Max of last two	Moving Range	Average Moving Range Selected
02/05/2017	Flint	11.79	11.93	135072
05/05/2017	Flint	11.87	11.93	135072	11.79	11.87	0.08	194.64
09/05/2017	Green	12.20	12.24	135074
10/05/2017	Green	12.21	12.24	135074	12.2	12.21	0.01	24.33
15/05/2017	Green	12.30	12.24	135074	12.21	12.30	0.09	218.97
19/05/2017	Flint	12.02	11.93	135072	12.02	12.30	0.28	681.24
22/05/2017	Flint	12.02	11.93	135072	12.02	12.02	0.00	0.00
24/05/2017	Flint	11.96	11.93	135072	11.96	12.02	0.06	145.98
29/05/2017	Flint	11.97	11.93	135072	11.96	11.97	0.01	24.33
06/06/2017	Flint	11.84	11.93	135072	11.84	11.97	0.13	316.29
12/06/2017	Flint	11.96	11.93	135072	11.84	11.96	0.12	291.96
19/06/2017	Flint	11.94	11.93	135072	11.94	11.96	0.02	48.66
26/06/2017	Flint	11.90	11.93	135072	11.9	11.94	0.04	97.32

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[gazpage]

Well that was pretty dumb as that's exactly what I just said wouldn't work. I am not sure I can understand why the outputs are what they are in that column so not sure my fix below is going to help.

Average Moving Range Selected=
IF(ISBLANK( [Moving Range] ),
    BLANK(),
    SUMX(
        ALLSELECTED ( QryAnalysis[Sample Date] ),
        CALCULATE ( [Max of last two]-[Min of last two] )
    )
)

What do you get from COUNTROWS ( QryAnalysis[Sample Date] ), out of interest? You may want to post another thread as you are beyond my skill level and maybe no one else is reading this at this point.

 

[gazpage]

Sorry, I meant COUNTROWS ( ALLSELECTED ( QryAnalysis[Sample Date] )) when added in your table.

 

[peter789]

I'm fairly sure that's done it! (I've changed SUMX back to AVERAGEX as I was only using the Sum to see what was happening)
Results:

Sample Date	Colour Group	Average Value	Average Selected values	Starting Index	Min of last two	Max of last two	Moving Range	Average Moving Range Selected
02/05/2017	Flint	11.79	11.93	135072
05/05/2017	Flint	11.87	11.93	135072	11.79	11.87	0.080	0.082
09/05/2017	Green	12.20	12.24	135074
10/05/2017	Green	12.21	12.24	135074	12.2	12.21	0.010	0.050
15/05/2017	Green	12.30	12.24	135074	12.21	12.30	0.090	0.050
19/05/2017	Flint	12.02	11.93	135072	12.02	12.30	0.280	0.082
22/05/2017	Flint	12.02	11.93	135072	12.02	12.02	0.000	0.082
24/05/2017	Flint	11.96	11.93	135072	11.96	12.02	0.060	0.082
29/05/2017	Flint	11.97	11.93	135072	11.96	11.97	0.010	0.082
06/06/2017	Flint	11.84	11.93	135072	11.84	11.97	0.130	0.082
12/06/2017	Flint	11.96	11.93	135072	11.84	11.96	0.120	0.082
19/06/2017	Flint	11.94	11.93	135072	11.94	11.96	0.020	0.082
26/06/2017	Flint	11.90	11.93	135072	11.9	11.94	0.040	0.082

<colgroup><col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> </colgroup><tbody>
</tbody>

I didn't run a COUNTROWS but I could see all the figures were being multiplied by 2433. I guess this was the number of Sample Dates being found when CALCULATE removed the higher level Pivot table filters operating that I haven't shown in my examples (YEAR, MONTH, Production Facility). I'm slightly reluctant to undo the latest changes to find out exactly; I hope you don't mind. You've certainly shown me a couple of uses of ALLSELECTED within an aggregate function that I hadn't read about, firstly qualifying the table name by column and secondly, what I still don't understand, is using SUMX without a Table name.
Many thanks again.