Difference between two rows

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"

SSS

Hi SSS,

If you have a calendar table and there is a relatioship from the date column of your base data table to the date column of the calendar table, then you can use this calculated field formula

=CALCULATE(SUM(Data[Orders]),PREVIOUSDAY(Calendar{Date]))-SUM(Data[Orders])

Thanks Guys!! It really helped!

Hey guys,

I encountered one problem I simply can't solve. If anyone can help me out I would be extra grateful!

I want to calculate the difference between two rows, but after I import data to the data model, the sort order is mixed up (see below: Report Date and Account Name) so one of my formulas that could work if the sort order is correct doesn't work.

The table looks like this and the Change column like this is my goal.

You can notice that the account doesn't have the correct order either by the report date. 

If there is any way how to solve it (maybe as a measure? or maybe is there a way how to correctly import data?)
I would be so so grateful.

All the best,

Tomislav

Hello Guys,

I have the same question on how to find the difference between two rows from the dated column. Below is how my table looks.

I just want the difference between the quantity column. I have tried multiple queries but it works fine in Import Mode but mine is Direct Query mode. Looks like there are many limitations in Direct Query. Could someone help me in giving some guidance regarding this for DIrect Query?

Thanks in advance.

I have another case. I would like to calculate the difference between rows. For example for 10-22

77-73=4
73-67=6
67-59=8

etc...

I have a similar problem.

I have 2 different datasets (for example sake, let's say volume table and ranking table). In the volume dataset, I have a measure that calculates the sum of the volume for each material based on an indicator column if it was received or issued. In the ranking table, the plant, material, plant volume limit, and the plant volume limit change date. I created a column in the ranking table called ranking that gives a numerical rank of the oldest plant volume limit change date from earliest (rank 1) to the latest for each material at each plant. I would like to accomplish 2 things: 

1. Calculate the change in plant volume limit between 2 dates for each material and each plant (i.e. the plant volume limit change between rank 1 & 2 as well as 2 & 3 for each material at each plant).
2. Show the volume measure in the ranking table for each material at each plant. When I currently pull it, it shows the sum of all the volumes for every row. How do I fix this?

Here is the ranking table