Forum Discussion
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 ...
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
Community Support
Community SupportHi 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
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.
| Melt | Serial | Length |
| 567110 | 5776 | 134.9629 |
| 567110 | 5777 | 134.9604 |
| 567110 | 5778 | 134.9629 |
| 567110 | 5779 | 134.9654 |
| 567110 | 5780 | 134.9604 |
| 567110 | 5781 | 134.9527 |
| 567110 | 5782 | 134.9477 |
| 567110 | 5783 | 134.9400 |
| 567110 | 5784 | 134.9756 |
Thank you in advance your your help.