Hello,

You can use the same formula as above (Calculated Column) and use ALLEXCEPT in the filter to filter all values except for the Product value. In my example, it should be :

SalesVariation = SALES[SalesAmount]-CALCULATE(SUM(SALES[SalesAmount]);FILTER(ALLEXCEPT(SALES, SALES[Product]);SALES[Index.1]=EARLIER(SALES[Index])))

I hope it helps, I don't have much time to help today :(

Have a nice day,

------------------------------

Jonathan CHANAL

Chef de Projet Digital / Data Analyst

MCSA Power BI & Excel

------------------------------

Original Message:

Sent: Feb 16, 2021 03:02 AM

From: Sturza Roman

Subject: subtract between two rows of the same column

Hi Jonathan,

Thank you very much for your help.

I have an issue I am struggling with and if you'll find few minutes to have a look, will be really thankful.

Basically I have a table with Products, Dates and Values (attached pbix file - Table1). I need to substract values from same column (Value column) for filtered product.

I created a calculated column (attached pbix file - Table2) but it works only when I have a table with a single product, also, I tried to add an index but it's not filtering properly the dates.

Below I'll attach the .pbix file and few snips with the expected outcome for Table 1.

------------------------------

Sturza Roman

Power BI

Original Message:

Sent: Feb 18, 2020 02:35 PM

From: Jonathan CHANAL

Subject: subtract between two rows of the same column

Hello,

You have to create two index columns in Power Query, sort the data first. An index starting from 0 and an index starting from 1. Then in a formula, you have to use the EARLIER function to perform the calculation in a calculated column.

Example for TotalSales (As I'm in France, I use ";" but I think you just have to replace it by ","):

SalesVariation = SALES[SalesAmount]-CALCULATE(SUM(SALES[SalesAmount]);FILTER(SALES;SALES[Index.1]=EARLIER(SALES[Index])))

Good luck,

------------------------------

Jonathan CHANAL

Chef de Projet Digital / Data Analyst

Grenoble

Original Message:

Sent: Feb 18, 2020 11:45 AM

From: Nadjim Idrs

Subject: subtract between two rows of the same column

Hello, I am trying to subtract between two rows of the same column, and put the result in another column.

thank you for any help

------------------------------

Nadjim

------------------------------