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

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.