Forum Discussion
Solved
Subtract Current Row from Previous row in another column
Hello Team
I am new to dax kindly assist with a formula that will give me the result below. I want column A to alway have the value of the previous row in Column C.
| Column A | Column B | Difference |
| 1,000.00 | 100.00 | 900.00 |
| 900.00 | 50.00 | 850.00 |
| 850.00 | 200 | 650.00 |
| 650.00 | 50.00 | 600.00 |
Thank you
You would need to add an index column to your data in Power Query.
Column D = VAR currentindex = Sheet1[Index] RETURN IF ( Sheet1[Column A] = BLANK (), CALCULATE ( MIN ( Sheet1[Column C] ), FILTER ( Sheet1, Sheet1[Index] = currentindex - 1 ) ), Sheet1[Column A] - [Column B] )Paul
6 Replies
- JarroVGIT
Resident Rockstar
How does your data currently look like? Where does columnA first row come from? It is not impossible to add a column to a table that is based on the previous row, but we are going to need a little context. Based on your example, one could assume that the third column is calculated but you are asking for the first column (which would be necessary to have your data at all?).
Please elaborate so we can help you out:)See the expected result below
bilyafamily
It seems you want to make change on Column A. But in Power BI, it is impossible to edit the original columns from databases, you can only create a new column that returns you expected value. In this case, we would need the original version of the your sample or show where are the 3 columns come from.
Best,
PaulOriginal table looks like this
Column A Column B Column C 1,000.00 100.00 900.00 50.00 850 200 650.00 50.00 600.00 Expected Result should look this this
Column A Column B Column C Column D 1,000.00 100.00 900.00 900 50.00 850 C1 200 650.00 C2 50.00 600.00 C3 You would need to add an index column to your data in Power Query.
Column D = VAR currentindex = Sheet1[Index] RETURN IF ( Sheet1[Column A] = BLANK (), CALCULATE ( MIN ( Sheet1[Column C] ), FILTER ( Sheet1, Sheet1[Index] = currentindex - 1 ) ), Sheet1[Column A] - [Column B] )Paul
