Subtracting Values row wise

[azizshaikh]

Hello everyone,

We are trying, subtract values from two rows present on the same column by identifying the value from a different column.

To understand the scenario better, here's an example

As seen in the above chart, we would like to subtract values from Qtr 2 to Qtr 1 and display the difference in a new column, here the challenge is, all the Qtr are present in a single column and their appropriate values. eg. shown below.

We had already tried PreviousMonth, PreviousYear functionality but ended up with unsatisfactory solutions.

Kindly let us know about your inputs.

[Anonymous · Accepted solution]

Hi @azizshaikh,

Based on your description, you want to compare your current row with previous row, right? If that is the case, firstly, use your table according the Amount column by using the Sort Ascending option.

Secondly, add an index column in your current table.

Thirdly, add a new column to compare rows values.
Difference = Table1[Amount]-IF(Table1[Index]=0,Table1[Amount],LOOKUPVALUE(Table1[Amount],Table1[Index],Table1[Index]-1))

For more details, you can review the example in the attached PBIX file.

Reference: http://ignoringthevoices.blogspot.sg/2016/02/comparing-with-previous-row-using.html



Thanks,
Lydia Zhang