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                                     testresult        movingrange

s00u0invertibility20jan        35                        

s00u0invertibility21jan        40                      5   absolute value(35-40)

soou0invertibility22jan        42                      2  absolute value(40-42)

 

How can we use measures to create a moving range?

Please help!!

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

     

15 Replies

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

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

    Hi,

    If you have date as a seperate column, then it is quite easy to solve this.