Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ...
Solved! Go to 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.
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.
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 ...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.