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

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-frfei-msft ,

     

    Thanks for the solution!!

  • amlopez45's avatar
    amlopez45
    Frequent Visitor
    7 years ago

    v-frfei-msft 

     

    I have a question about the M Code.  What would I need to change for the M code to work on my file? I created my index column from 1 and on. 

    MeltSerialLength 
    5671105776134.9629
    5671105777134.9604
    5671105778134.9629
    5671105779134.9654
    5671105780134.9604
    5671105781134.9527
    5671105782134.9477
    5671105783134.9400
    5671105784134.9756

     

    Thank you in advance your your help.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      Describe your question.  Also, show the result you are expecting.

      • 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!