Forum Discussion
Difference between two rows
Hi SSS
Using DAX you can add this calculated column to get desired results
= VAR NextIndex = Table1[Index] + 1 RETURN Table1[Orders] - CALCULATE ( VALUES ( Table1[Orders] ), FILTER ( ALL ( Table1 ), Table1[Index] = NextIndex ) )
It looks lik you are a looking for a solution in Power Query?
In general, if you want to compare values from different rows, you can add 2 index columns, one starting with 0 and the other with 1.
Then you merge the query with itself, using the index columns as merge columns.
If you want the data from the previous row on the current row, then you should merge on Index 0 and Index 1 (in this sequence);
if you want the data from the next row on the current row, then you should mergen on Index 1 and Index 0.
After merging, adjust the generated code and name your column "Previous" or "Next".
Exoand, using the original column name as prefix,
After expanding sort on one of the original index columns.
Below the code for your case.
let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Index.0", 0, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Index",{"Index.0"},"Next",JoinKind.LeftOuter),
#"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Orders"}, {"Next.Orders"}),
#"Sorted Rows" = Table.Sort(#"Expanded Next",{{"Index", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Difference", each [Orders] - [Next.Orders]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index.0", "Next.Orders"})
in
#"Removed Columns"
Well, that's a neat trick! This thread showed up on a google search, and it's exactly what I needed. So I just want to let you know that your reply still lives on and helps people 🙂
I've never thought of merging a table with itself on different indexes, but now you've mentioned it, it's such an obvious and elegant solution. I would ever have thought of this myself, so a big thanks from me!