Need help in creating calculated column based on the same column previous row

[Thimma_pbi]

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.

[v-prasare]

@Thimma_pbi ,

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

[v-prasare]

@Thimma_pbi ,

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

[v-prasare]

@Thimma_pbi ,

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

[burakkaragoz]

Hi @Thimma_pbi ,

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 AI

[pankajnamekar25 · Accepted solution]

Hello @Thimma_pbi 

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

1. Add an Index column.
2. Add a custom column to compute the value based on the previous row.
3. 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 | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Thimma_pbi]

It has to be done at calculated column only.

[maruthisp]

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

[Ashish_Excel]

Very confusing.  The value in the second row is 14 (not 40).  Where does 40 come from?

[Thimma_pbi]

40 is the additional calculation value - previous row value.

[Shravan133]

1. In Power Query:

• Add an Index column.

• Sort the table by Dt, then Level 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