# Moving Range calculation for I-MR control chart



## peter789 (Jul 13, 2017)

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:

OxideNa2O %Average ValueSample DateIndexAmber03/04/2017
13295011.9710/04/2017
13295112.0018/04/201713295211.9624/04/201713295312.0902/05/201713295412.1108/05/201713295512.0715/05/201713295612.0822/05/2017
13295712.08
29/05/2017
13295811.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 (Jul 13, 2017)

With your index column you can then use LOOKUPVALUE() I would think.


```
New Column:=
min(
    LOOKUPVALUE( 
        Data[Value] , 
        Data[Index] , 
        Data[Index]+1
    ),Data[Value]
)
```


----------



## peter789 (Jul 13, 2017)

Thanks for the suggestion.  So that would involve adding calculated columns to the data Model? 
I was hoping to manage it with a measure.


----------



## gazpage (Jul 13, 2017)

Well you could put that in a column and you would get a value for each row to see it's working, but you could just as well use that as part of an iterator in a measure. 

I've never used it in anger, but worst case you could use ADDCOLUMNS in a measure to create that column on the fly.


----------



## peter789 (Jul 13, 2017)

Thanks. I'll try it tomorrow.


----------



## peter789 (Jul 14, 2017)

Thanks to the earlier help I've moved forward and have now calculated my average Moving Range.

Oxide
Values
Na2O %
Sample Date
Colour Group
Average Value
Colour Average Values
Moving Range
Colour Average Moving Range
03/04/2017
Flint
11.92
11.93
0.070
0.083
10/04/2017
Flint
12.02
11.93
0.100
0.083
18/04/2017
Flint
11.87
11.93
0.150
0.083
24/04/2017
Flint
11.82
11.93
0.050
0.083
02/05/2017
Flint
11.79
11.93
0.030
0.083
05/05/2017
Flint
11.87
11.93
0.080
0.083
09/05/2017
Green
12.20
12.24
0.330
0.143
10/05/2017
Green
12.21
12.24
0.010
0.143
15/05/2017
Green
12.30
12.24
0.090
0.143
19/05/2017
Flint
12.02
11.93
0.280
0.083
22/05/2017
Flint
12.02
11.93
0.000
0.083
24/05/2017
Flint
11.96
11.93
0.060
0.083
29/05/2017
Flint
11.97
11.93
0.010
0.083


<tbody>

</tbody>
I used the following measure for the minimum of the current and previous value (Max was similar):

=MINX(QryAnalysis,
    MIN(LOOKUPVALUE( 
        QryAnalysis[Value] , 
     QryAnalysis[Index] , 
      QryAnalysis[Index]-1
    ),QryAnalysis[Value]
))

Note that the previous not the next row should have been compared (now corrected by changing + to -1). 

However a list of moving ranges should contain one less items than the original values. The very first value as shown in the Pivot should be blank.  The [Index] -1 operates despite the filter context of the report as it is working on the original table column.
I guess I am now looking for some way of filtering the range of Index values in the LOOKUPVALUE expression but am not really sure how to go about it; should I be trying to wrap it up inside a CALCULATE FILTER formula?  Again any pointers from anyone would be most appreciated please.


----------



## gazpage (Jul 14, 2017)

If there is no match for LOOKUPVALUE then it returns a BLANK(). So for your first row it is looking up an INDEX of 0 and won't find anything. I would imagine a MIN just ignores a BLANK() so MIN ( BLANK, FirstRow ) will just equal the first row.

I am not sure CALCULATE FILTER will help as you could cut out the first row, but then it effectively won't exist for the second line calc either. I think your best bet is to use an IF()


```
=MINX (
    QryAnalysis,
    IF (
        QryAnalysis[Index] = 1,
        BLANK(),
        MIN(
            LOOKUPVALUE( 
                QryAnalysis[Value] , 
                QryAnalysis[Index] , 
                QryAnalysis[Index]-1
            ),
            QryAnalysis[Value]
        )
    )
)
```


----------



## peter789 (Jul 14, 2017)

Thanks.  Unfortunately the starting value of Index isn't always 1 as it depends on the table filters how far you are down the list of over 200,000 rows (see my first post for an example).  I've tried your formula but with QryAnalysis[Index] = 1 changed to QryAnalysis[Index] = "Value of first index number in selected group".  If I manually enter the index number it tests out OK but so far haven't been able to work out an expression for the initial index number which doesn't recalculate itself as each row iterates. I tried Min Index Allselected Sample dates without success.


----------



## gazpage (Jul 14, 2017)

If it's always the lowest index number you need to remove then just replace the = 1 with = MIN ( QryAnalysis[Index] ). The MIN will be calculated in the current filter context. I would have expected this to work as aggregators are calculated in the filter context, not the row context.

If not then try MIN ( EARLIER ( QryAnalysis[Index] ).


----------



## peter789 (Jul 15, 2017)

Unfortunately I have had little success using the straightforward expression shown above.  I think I vaguely get what EARLIER should do but cannot seem to be able to apply it in a measure. I have tried the following:
First create a measure called Starting index to find the lowest Index value in the filter context.  

Starting Index:=IF(ISBLANK([Average Value]),BLANK(),CALCULATE(MIN(QryAnalysis[index]),ALLSELECTED(QryAnalysis[sample date])))

So far so good; this produces exactly the index number I am looking for in the correct row.

I then place it in the following measure.

Min of last two:=MINX (    QryAnalysis,    IF (
        QryAnalysis[Index] =[Starting Index],        BLANK(),
        MIN(
            LOOKUPVALUE( 
                QryAnalysis[Value] , 
                QryAnalysis[Index] , 
                QryAnalysis[Index]-1
            ),
            QryAnalysis[Value]
        )
    )
)
This calculates a Blank in every row.  To test I changed the BLANK() to [Starting Index] to see what is happening.

The resulting table looks like:


OxideValuesNa2O %Sample DateColour GroupAverage ValueStarting IndexMin of last two06/06/2017Flint11.8413508113508112/06/2017Flint11.9613508113508219/06/2017Flint11.9413508113508326/06/2017Flint11.9013508113508403/07/2017Flint11.9813508113508505/07/2017Green12.1613508613508610/07/2017Green12.26135086135087

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

</tbody>
It looks like the row context is being applied and causing the [Starting Index] measure to be filtered before the IF looks at it.
I seem to have been banging my head on a wall with this for most of yesterday evening.  Help and advice to the hard of thinking would again be most gratefully received!


----------



## peter789 (Jul 13, 2017)

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:

OxideNa2O %Average ValueSample DateIndexAmber03/04/2017
13295011.9710/04/2017
13295112.0018/04/201713295211.9624/04/201713295312.0902/05/201713295412.1108/05/201713295512.0715/05/201713295612.0822/05/2017
13295712.08
29/05/2017
13295811.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 (Jul 15, 2017)

Yeah, that won't work.

Using a measure in another DAX formula puts an implicit CALCULATE around it, and CALCULATE converts an existing row context into a filter context. In your formula that means [Starting Index] is calculated in the context of the current row if the MINX iteration. The only way around this is to breakout the measure in full

I think you need to remove the CALCULATE in [Starting Index] (not sure why it's there, but it will cause the same problem as above) and then replace [Starting Index] in the second measure with the full formula broken out.


----------



## peter789 (Jul 15, 2017)

I'm not sure how to take the CALCULATE out as the MIN calculate needs to operate with the ALLSELECTED [sample date] to calculate the lowest index value of each group. Without it I think the MIN operates on each row and is effectively redundant as it just returns the original value of Index?


----------



## gazpage (Jul 15, 2017)

Who boy! This is a doozy!

Not sure I understand well enough your calculation, so sorry if I give bad advice. The below is a bit of a shot gun approach of the things I would try if I was the one working in your data. I hope this is more helpful than not doing anything. I am finding it hard to work out how to break out of the row context that is transitioned into a filter context bu CALCULATE.

First to check that you are not using Excel 2016 or Power BI desktop. If you are then we should make Starting Index a variable and I think it would work then. 

Second, try putting the all the other columns in the that you don't need to apply to work out the minimum in the ALLSELECTED. e.g ALLSELECTED(QryAnalysis[sample date], QryAnalysis[INDEX] etc). Only leaving teh Colour Group and any other items that are required to pin down the context to evaluate the minimum.

Finally, maybe we need to rebuild the evaluation context entirely for Starting Index so it is something like.


```
CALCULATE ( 
    MIN ( QryAnalysis[Index] ),
    FILTER ( 
        ALLSELECTED ( QryAnalysis ),
        QryAnalysis[ColourGroup] = EARLIEST (QryAnalysis[ColourGroup],
        OTHER CONDITIONS THAT NEED TO BE APPLIED TO WORK OUT THE MIN INDEX
    )
)(
```

My logic is that by using ALLSELECTED you filter the entire table, ignoring any existing filter contexts other than the SELECTED ones. Then pick up all the rows you need and take the min of the resulting table. I've never used EARLIEST before, but I figure you need to break out of both the evaluation context in my FILTER iteration, and also in the MINX row context.

Sorry not to be more help.


----------



## peter789 (Jul 15, 2017)

Thanks again and no need to apologise, you've helped me move much further forward than I would have managed with weeks of stumbling around, I really appreciate the time you've spent so far with my esoteric problem (might be why I've not seen Moving Range covered in any of the statistical calculations in DAX I've googled)!  You've given me plenty to work on. (By the way to complicate matters [Colour Group] is in a related table so I'll have to think about that). Also I hadn't realised you can have multiple columns in ALLSELECTED so that looks promising too.
However you gave me a lifeline with your second paragraph: I am working with Excel 2016.  I have read about VAR but skimmed past it, I'll go back and have a good read.  Am I right in thinking I can declare [Staring Index] as a variable and then call it from the measure when it will override the implicit CALCULATE action?
Rgds
Peter


----------



## peter789 (Jul 15, 2017)

Fantastic, VAR did everything I asked it to do!

OxideValuesNa2O %Sample DateColour GroupAverage ValueMin of last two03/04/2017Flint11.9210/04/2017Flint12.0211.9218/04/2017Flint11.8711.8724/04/2017Flint11.8211.8202/05/2017Flint11.7911.7905/05/2017Flint11.8711.7909/05/2017Green12.2010/05/2017Green12.2112.215/05/2017Green12.3012.2119/05/2017Flint12.0212.0222/05/2017Flint12.0212.0224/05/2017Flint11.9611.9629/05/2017Flint11.9711.9606/06/2017Flint11.8411.8412/06/2017Flint11.9611.8419/06/2017Flint11.9411.9426/06/2017Flint11.9011.9


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

</tbody>
However all it's shown me now is that I hadn't thought my problem right the way through and I need to consider the case where a colour group repeats in the data selected by the table filter! But that's my problem for now!


----------



## gazpage (Jul 15, 2017)

https://www.sqlbi.com/articles/variables-in-dax/

These guys will explain it better than I ever could. But variables are calculated in the context which they are defined, not when they are called.

Given starting index was working for you on its own I reckon that's your best solution.


----------



## gazpage (Jul 15, 2017)

Yeah, Variables are great. Make things easy that would otherwise be a massive pain. 

Happy to keep trying to help if I can. I note that instead of using LOOKUPVALUE, you could have filtered for the next lowest INDEX.


----------



## peter789 (Jul 17, 2017)

I'm afraid I've got stuck again.  I can now calculate the Minimum of the last two values and the Maximum of the last two values, I can then subtract Min from Max and calculate the Moving Range. Now all I need to do is work out the average of the Moving Range in the same way I have been successfully working out average Value using ALLSELECTED.  The results are not what I hoped to achieve and I have a hazy idea of why it's happening but I'm baffled as to how I can sort it out.  I was hoping the following measure would carry out the calculation; to show what it is actually doing I've changed AVERAGEX to SUMX.

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

The results are:



Sample DateColour GroupAverage ValueAverage Selected valuesStarting IndexMin of last twoMax of last twoMoving RangeAverage Moving Range Selected02/05/2017Flint11.7911.9313507205/05/2017Flint11.8711.9313507211.7911.870.080.8009/05/2017Green12.2012.2413507410/05/2017Green12.2112.2413507412.212.210.010.0315/05/2017Green12.3012.2413507412.2112.300.090.2719/05/2017Flint12.0211.9313507212.0212.300.282.8022/05/2017Flint12.0211.9313507212.0212.020.000.0024/05/2017Flint11.9611.9313507211.9612.020.060.6029/05/2017Flint11.9711.9313507211.9611.970.010.1006/06/2017Flint11.8411.9313507211.8411.970.131.3012/06/2017Flint11.9611.9313507211.8411.960.121.2019/06/2017Flint11.9411.9313507211.9411.960.020.2026/06/2017Flint11.9011.9313507211.911.940.040.40

<tbody>

</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
The aggregation seems to be operating on each row but summing itself by the total number of rows in the group (either 3 or 10 in this example).  I have tried with and without the use of VAR with no change in the result.  I am also concerned that I must be tackling the problem in a very inefficient manner as it is taking nearly 20 seconds to recalculate the table.
As before any help or suggestions would be gratefully received please!


----------



## gazpage (Jul 17, 2017)

What values are you expecting to see? Seems like you now have the opposite problem that you had before.

varMR is calculated where it is defined, in your case it's in the rows of your table. So varMR in the 05/05 row is 0.08 and is fixed for the rest of the calculation. Your SUMX then cycles through the table with the filter on sample data removed, so again for row 05/05 it is cycling through all the Flint rows (10) and each time adding the pre-calculated 0.08, so a total of 0.8.

I imagine we were to follow the calculation chain back we would probably find that we have iterations within iterations within iterations. I'm afraid improving that is probably beyond my knowledge.


----------



## peter789 (Jul 17, 2017)

I didn't explain that well.  I expect the Average Moving Range Selected to look like the Average selected Values column.  i.e. All the Flint as 0.82 and the Green as 0.30.  I've tried it without the defined Variables and it calculates in exactly the same way as above.


----------



## peter789 (Jul 13, 2017)

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:

OxideNa2O %Average ValueSample DateIndexAmber03/04/2017
13295011.9710/04/2017
13295112.0018/04/201713295211.9624/04/201713295312.0902/05/201713295412.1108/05/201713295512.0715/05/201713295612.0822/05/2017
13295712.08
29/05/2017
13295811.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 (Jul 17, 2017)

```
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 (Jul 17, 2017)

Unfortunately:


Sample DateColour GroupAverage ValueAverage Selected valuesStarting IndexMin of last twoMax of last twoMoving RangeAverage Moving Range Selected02/05/2017Flint11.7911.9313507205/05/2017Flint11.8711.9313507211.7911.870.08194.6409/05/2017Green12.2012.2413507410/05/2017Green12.2112.2413507412.212.210.0124.3315/05/2017Green12.3012.2413507412.2112.300.09218.9719/05/2017Flint12.0211.9313507212.0212.300.28681.2422/05/2017Flint12.0211.9313507212.0212.020.000.0024/05/2017Flint11.9611.9313507211.9612.020.06145.9829/05/2017Flint11.9711.9313507211.9611.970.0124.3306/06/2017Flint11.8411.9313507211.8411.970.13316.2912/06/2017Flint11.9611.9313507211.8411.960.12291.9619/06/2017Flint11.9411.9313507211.9411.960.0248.6626/06/2017Flint11.9011.9313507211.911.940.0497.32

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

</tbody>


----------



## gazpage (Jul 17, 2017)

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 (Jul 17, 2017)

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


----------



## peter789 (Jul 17, 2017)

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 DateColour GroupAverage ValueAverage Selected valuesStarting IndexMin of last twoMax of last twoMoving RangeAverage Moving Range Selected02/05/2017Flint11.7911.9313507205/05/2017Flint11.8711.9313507211.7911.870.0800.08209/05/2017Green12.2012.2413507410/05/2017Green12.2112.2413507412.212.210.0100.05015/05/2017Green12.3012.2413507412.2112.300.0900.05019/05/2017Flint12.0211.9313507212.0212.300.2800.08222/05/2017Flint12.0211.9313507212.0212.020.0000.08224/05/2017Flint11.9611.9313507211.9612.020.0600.08229/05/2017Flint11.9711.9313507211.9611.970.0100.08206/06/2017Flint11.8411.9313507211.8411.970.1300.08212/06/2017Flint11.9611.9313507211.8411.960.1200.08219/06/2017Flint11.9411.9313507211.9411.960.0200.08226/06/2017Flint11.9011.9313507211.911.940.0400.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.


----------

