List.Generate to calculate column based on the previous row value of the Same Column

Hi DSR  , Could you try this 

let
// Example table
Source = Table.FromRows(
{
{"A", 0, "Yr00", 0, 2, 0, 0},
{"A", 1, "Yr01", 0, 2, 1, 1},
{"A", 2, "Yr02", 1, 2, 2, 1},
{"A", 3, "Yr03", 1, 2, 3, 1},
{"A", 4, "Yr04", 1, 2, 4, 0},
{"A", 5, "Yr05", 0, 2, 5, 1},
{"A", 6, "Yr06", 1, 2, 6, 0},
{"A", 7, "Yr07", 0, 2, 7, 0},
{"A", 8, "Yr08", 0, 2, 8, 0},
{"A", 9, "Yr09", 0, 2, 9, 0},
{"A", 10, "Yr10", 0, 2, 10, 0}
},
{"Case.CC.EEName", "PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE"}
),

// Generate calculated column
AddedColumn = Table.AddColumn(Source, "CalculatedColumn", each null),
ListValues = List.Generate(
() => [Index = 0, PreviousValue = 0], // Start condition
each [Index] < Table.RowCount(Source), // Continue while there are rows
each [
Index = [Index] + 1,
PreviousValue = if Table.Column(Source, "Index.inGrp"){[Index]} = 0 then
0
else if Table.Column(Source, "NoEE"){[Index]} = 0 or Table.Column(Source, "NoEE"){[Index]} = null then
null
else if [PreviousValue] = Table.Column(Source, "Yr Move Out"){[Index]} then
Table.Column(Source, "NoEE"){[Index]}
else
[PreviousValue] + 1
],
each [PreviousValue]
),

// Add ListValues back to the table
Result = Table.FromColumns(Table.ToColumns(Source) & {ListValues}, Table.ColumnNames(Source) & {"CalculatedColumn"})
in
Result
In the above 

• Recursive Calculation: List.Generate tracks the previous row's value and uses it for the next row.
• Conditions: Checks are implemented for Index.inGrp , NoEE , and Yr Move Out values as specified.
• Merge Back: The calculated list is combined back into the table.
  If this post helped please do give a kudos and accept this as a solution 
  Thanks In Advance

Hello DSR,

Thank you for posting your query in the Microsoft Fabric Community Forum regarding the calculation of a column based on the previous row's value using List.Generate in Power Query. I have successfully replicated your scenario in Power BI Desktop and would like to share the steps I followed, along with the expected output and a PBIX file for your reference.

Steps I Followed:

• Created Sample Data: I manually entered the following data into Power BI Desktop:

• Opened Power Query Editor: I imported the data into Power Query by selecting Transform Data.
• Added Custom Calculated Column: I implemented the following M code to create the new calculated column, CalculatedNoEE:

Code:

let 

    index = [Index.inGrp], 

    noEE = [NoEE], 

    yrMoveOut = [Yr Move Out], 

    previousValues = List.Generate( 

        () => [Value = 0, Index = 0],

        each [Index] <= index,

        each [ 

            Value = if [Index] = 0 then 0

                     else if noEE = 0 or noEE = null then null

                     else if [Value] = yrMoveOut then noEE  

                     else [Value] + 1,

            Index = [Index] + 1   

        ], 

        each [Value]

    ), 

    result = List.Last(previousValues)

in 

    result 

• Reviewed and Applied Changes: After adding the custom column, I verified the calculations.

• I have attached a screenshot of the output table showing the newly calculated column:
  

   

• Additionally, I have attached the PBIX file for your convenience.

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.
Hope this works for you!
Thank you.

Hi DSR, check this:

Output

let
    Source = Table.FromRows( {
        {"A", 0, "Yr00", 0, 2, 0, 0},
        {"A", 1, "Yr01", 0, 2, 1, 1},
        {"A", 2, "Yr02", 1, 2, 2, 1},
        {"A", 3, "Yr03", 1, 2, 3, 1},
        {"A", 4, "Yr04", 1, 2, 4, 0},
        {"A", 5, "Yr05", 0, 2, 5, 1},
        {"A", 6, "Yr06", 1, 2, 6, 0},
        {"A", 7, "Yr07", 0, 2, 7, 0},
        {"A", 8, "Yr08", 0, 2, 8, 0},
        {"A", 9, "Yr09", 0, 2, 9, 0},
        {"A", 10, "Yr10", 0, 2, 10, 0} },
        {"Case.CC.EEName", "PeriodNo", "Yr", "NoEE.inPrvPrd", "Yr Move Out", "Index.inGrp", "NoEE"} ),
    L = List.Buffer(Table.ToRows(Table.SelectColumns(Source,{"NoEE", "Index.inGrp", "Yr Move Out"}))),
    Gen = List.Skip(List.Generate(
        ()=> [ x = -1],
        each [x] < List.Count(L),
        each [ x = [x]+1, y = L{x},
               z = if (y{0}??0) = 0 then null
                   else if (y{1}??0) = 0 then null
                   else if ([z]??0) = 0 then 1
                   else if [z] = y{2} then y{0}
                   else [z] +1 ],
        each [z] )),
    Merged = Table.FromColumns(Table.ToColumns(Source) & {Gen}, Value.Type(Table.FirstN(Source, 0) & #table(type table[Answer=Int64.Type], {})))
in
    Merged

At the beginning you have to check List.Generate. 

Hi DSR,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hi DSR,

Thank you dufoq3 for addressing the issue.

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If the super user's response resolved your issue, please Accept it as a solution and consider leaving a "Kudos" so other members can find it more easily.

Thank you.

Hello DSR,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

 Akash_Varuna's result

was the nearest but not aligned

Thank you ffor your effort.

Yours are the nearest but they are not aligned.

I asked copilot (5-8), deepsaeek(10+) and openai (15+) times all failed. however, i took thast copilot and changed 2 line of codes to become:

CurrentRow = BufferTable{[RowIndex]+1},
PreviousRow = BufferTable{[RowIndex]},

Copilot as most looked back, i follower rickmaurinus  logic in Calculating running total.  looking  next instead of looking back

and correctly solve it: here is the correct codes: