cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Substract rows from the same column

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 ...

 

restar valores misma columna.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Substract rows from the same column

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.

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Substract rows from the same column

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.

 

View solution in original post

Re: Substract rows from the same column

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 ...

Super User
Super User

Re: Substract rows from the same column

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 293 members 3,430 guests
Please welcome our newest community members: