Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Moving range ( subtract previous row value from earlier row)

Hello Experts,   I am working to create a mvoing range chart which will show the diffrence between results of current row and previous row. The result i want is moving range column-   key       ...
Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    1. Insert an index column in power query as below.

     

     

    M code as below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjYwKDXIzDMyyErMU9JRMjZVitVBiBpCRE0MUESNoKJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"key", type text}, {"result", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

    2. Create the measure to get the exceoted result.

    Measure = var change =  CALCULATE(SUM(Table1[result]),FILTER(ALL(Table1),Table1[Index]=MAX(Table1[Index])-1))
return
IF(ISBLANK(change),BLANK(),MAX(Table1[result])-change)

    Pbix as attached.

     

    Regards,

    Frank

     

amlopez45's avatar
amlopez45
Frequent Visitor
7 years ago

Ashish_Mathur ,

 

I want to get the moving average of the data below.

 

MeltSerialLength (mm)Moving Range
5671105776134.9629 
5671105777134.9604

0.0025 = abs(134.9629-134.9604)

5671105778134.96290.0025
5671105779134.96540.0025
5671105780134.96040.0051
5671105781134.95270.0076
5671105782134.94770.0051
5671105783134.94000.0076
5671105784134.97560.0356

 

v-frfei-msft had a solution using M code. When I copied the code from here to my file I received an error (most likely from referencing columns that do not exist). I am not sure what part of the code should be changed to make it work. 

 

Hope that is enough information. Let me know if you have any other questions. Thank you!

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello amlopez45,

 

I have created a calculated measure using your data.

follow the steps-

1. add an index column

2. create a measure which will refer to the next value of the row.

3. create an Absolute difference between the current value and previous value.

 

refer attached images.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi @v-frfei-msft ,

    I need the average of the moving ranges. Is it possible to insert more steps in your calculated measure above and calculate the average? Please can you help me?