Forum Discussion
Need help in creating calculated column based on the same column previous row
Hi,
I have a table as mentioned below without New Column, I want to create a calculated column as New Column on 1st row initial value as ZERO(for L1) by default, but second row that L2 40-0 (40 is the value I get based on values and subtract previous row that is ZERO, again 3rd row L1 120-40 (120 is the value based on other calculations and subtract previous row value that 40 and I will get 80 and so on (120 is a kind of target value). Here is the question I am creating New column but taking reference of same column previous row, now sure how to get it. Sometimes I get the zero and subtract previous row
| Dt | Level | Clause1 | Clause2 | Val | New Column |
| 01-01-2025 | L1 | S | 10 | 0 | |
| 01-01-2025 | L2 | 14 | 40-0 | ||
| 02-01-2025 | L1 | E | S | 38 | 120-40=80 |
| 02-01-2025 | L2 | 44 | 120-80=40 | ||
| 03-01-2025 | L1 | 120-40=80 | |||
| 03-01-2025 | L2 | 33 | 0-80=-80 |
Any thoughts on the same and much appreciate your help.
Hello Anonymous
Power Query allows you to refer to previous rows, which is ideal here.
Steps
Sort your data by Dt and Level (or another stable ordering).
- Add an Index column.
- Add a custom column to compute the value based on the previous row.
- Use logic like
Custome= if [Index] = 0 then 0
else [Target] - #"Previous Step"{[Index]-1}[NewColumn]
for DAX Measure
New Measure =
VAR CurrentIndex = MAX('Table'[Index])
VAR PrevIndex = CurrentIndex - 1
VAR PrevValue =
CALCULATE(
MAX('Table'[YourTarget]),
FILTER('Table', 'Table'[Index] = PrevIndex)
)
RETURN
[Target] - PrevValue
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
10 Replies
1. In Power Query:
-
Add an Index column.
-
Sort the table by
Dt, thenLevel if needed.2. Write a Custom Column with this logic in M (Power Query):
let Source = YourTableName, AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1), AddRunningCalc = Table.AddColumn(AddIndex, "New Column", each if [Index] = 0 then 0 else let PrevRow = AddIndex{[Index]-1}, PrevVal = PrevRow[#"New Column"], Result = 120 - PrevVal in Result ) in AddRunningCalc -
-
Very confusing. The value in the second row is 14 (not 40). Where does 40 come from?
Hi Thimma_pbi,
Looks like above scenario in DAX couln't look up one row in a claucalted column, it creates circular reference.
Bteter to do it in Power Query:
1.Add Index column starts from zero.2.Add custom column:
PrevVal = if [Index]=0 then 0
else #"PreviousStep"[Val]{[Index]-1}
3.Add custom column as:
NewColumn = [Val] - [PrevVal]Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
Hello Anonymous
Power Query allows you to refer to previous rows, which is ideal here.
Steps
Sort your data by Dt and Level (or another stable ordering).
- Add an Index column.
- Add a custom column to compute the value based on the previous row.
- Use logic like
Custome= if [Index] = 0 then 0
else [Target] - #"Previous Step"{[Index]-1}[NewColumn]
for DAX Measure
New Measure =
VAR CurrentIndex = MAX('Table'[Index])
VAR PrevIndex = CurrentIndex - 1
VAR PrevValue =
CALCULATE(
MAX('Table'[YourTarget]),
FILTER('Table', 'Table'[Index] = PrevIndex)
)
RETURN
[Target] - PrevValue
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Hi Anonymous ,
You can achieve this using a calculated column with a bit of DAX logic. If you're trying to label the first and last row for each ID, here’s a simple example:
RowType = VAR CurrentID = Table[ID] VAR CurrentDate = Table[Date] VAR MinDate = CALCULATE(MIN(Table[Date]), ALLEXCEPT(Table, Table[ID])) VAR MaxDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[ID])) RETURN SWITCH( TRUE(), CurrentDate = MinDate, "Start", CurrentDate = MaxDate, "End", "Middle" )This assumes you have a Date column to determine order. You can replace it with any column that defines the row sequence.
Let me know if your logic is different—happy to tweak it!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AIAnonymous ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
burakkaragoz pankajnamekar25 maruthisp Ashish_Excel thanks all for your prompt response.
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
