Substract rows from the same column

[OscarSuarez10]

Hello, I´m tryin to substract rows from the same column (FPR), I need to substract the next value from the previous value acorrding to the date like this: FPR from 30/04/2019 - FPR from 31/03/2019, then 30/05/2019 - 30/04/2019 etc ...

[Nolock · Accepted solution]

Hi @OscarSuarez10,

sort the table by the column TIME, create an index column starting with 0 and use the following formula for addressing the previous row:

SortByTime = Table.Sort(#"Previous Step", {"TIME"}),
IndexColumn = Table.AddIndexColumn(SortByTime, "Index", 0, 1),
Substract = Table.AddColumn(IndexColumn, "Substract", each [FPR] - IndexColumn[FPR]{[Index] - 1})

The value of current row: [FPR] or IndexColumn[FPR]{[Index]}

The value of previous row: IndexColumn[FPR]{[Index] - 1} - give me the value of column FPR on the index [Index] - 1.

[OscarSuarez10]

Thank You for answering, that helped me a lot

But now I only have to substract the first value of 2019 and the last value of 2019, then the first value of 2020 and then the last value of 2020 etc ...

[Nolock]

Hi @OscarSuarez10,

I see.
Please close this topic and open a new one, because it requires another approach. You can also mention me and I'll try to respond as soon as possible.