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

     

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

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Table1[Length (mm)]),FILTER(Table1,Table1[Melt]=EARLIER(Table1[Melt])&&Table1[Serial]=EARLIER(Table1[Serial])-1))),BLANK(),ABS(Table1[Length (mm)]-CALCULATE(MAX(Table1[Length (mm)]),FILTER(Table1,Table1[Melt]=EARLIER(Table1[Melt])&&Table1[Serial]=EARLIER(Table1[Serial])-1))))

Hope this helps.

MuhamadIlhamsya's avatar
MuhamadIlhamsya
Frequent Visitor
2 years ago

Hi Ashish_Mathur 

 

can you please help me? want to have similar value but grouped by the parameter, so it substract the previous row but based on the same parameter

 

thanks!

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

    Hi,

    Not clear about your requirement.  Share data in a format that can be pasted in an MS Excel file and show the expected result.