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"
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 )
)thanks for the solution, it works perfectly
Can we make the difference as new row and named Difference?
Hi Zubair_Muhammad,
Congrats for the solution.
Can I take ask for a complementary demand on the same idea?
As the original demand, I need to execute the difference between two dates located in different rows, but I need to consider a key. It is something like this:
Key Date
1 01/08/2018
1 12/09/2018
1 05/10/2018
2 02/07/2018
2 16/08/2018
Thanks a lot in advance.
Regards.
i am actually looking to find the solution to the same usecase.
Please share if you find out anything
Zubair_Muhammad wrote: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 ) )And without Index column?
Hi Zubair,
Is there a way to do this in measure instead of calculated column? My situation is that I need to calculate the average of these differences. But my table changes every time user changes the filter in the visual. So creating one calculated column from a fixed table won't work for me. Thanks in advance!
Hi, I'm totally new to Power Query and DAX. I need a solution to calculate difference of a value beteen two rows, and tried this one. But when I create a new column and paste the code, I get the error Token Expected:
I can't find a solution to this problem, probably because I don't know DAX fundamentals. Thank you.
