circular dependency between 2 columns in SQL

Question

Asking for help as I am stuck with the following SQL problem:

basically I have 2 columns that are dependent to each other,

like column 1's calculation is partially based on the lag of column 2, and column 2 = some other values + column 1. Because the column 2 is not defined yet, when selecting column 1, I am facing an SQL error, any suggestion of what I could do?

Here is what I am trying to achieve:

Example Image As you can see in the image, I would like to select the lag of column Final_Stock (column 2), while this column is only defined after the Stock_Forecast column (column 1)

Full code with sample data

with
        tab as (
               select
                       'a' as material_code
                     , 1 as id
                     , 174639 as actual_stock
                     , 0 as incoming_stock
                     , 321 as demand_u
                     , 742 as plan_units
               union all
               select
                       'a' as material_code
                     , 2 as id
                     , 161028 as actual_stock
                     , 0 as incoming_stock
                     , 272 as demand_u
                     , 848 as plan_units
               union all
               select
                       'a' as material_code
                     , 3 as id
                     , 152807 as actual_stock
                     , 0 as incoming_stock
                     , 294 as demand_u
                     , 661 as plan_units
               union all
               select
                       'a' as material_code
                     , 4 as id
                     , 128859 as actual_stock
                     , 0 as incoming_stock
                     , 291 as demand_u
                     , 1285 as plan_units
               union all
               select
                       'a' as material_code
                     , 5 as id
                     , 131402 as actual_stock
                     , 0 as incoming_stock
                     , 276 as demand_u
                     , 1046 as plan_units
               union all
               select
                       'a' as material_code
                     , 6 as id
                     , 173586 as actual_stock
                     , 0 as incoming_stock
                     , 561 as demand_u
                     , 862 as plan_units
               union all
               select
                       'a' as material_code
                     , 7 as id
                     , 143460 as actual_stock
                     , 0 as incoming_stock
                     , 636 as demand_u
                     , 879 as plan_units
               union all
               select
                       'a' as material_code
                     , 8 as id
                     , 0 as actual_stock
                     , 0 as incoming_stock
                     , 0 as demand_u
                     , 635 as plan_units
               union all
               select
                       'a' as material_code
                     , 9 as id
                     , 0 as actual_stock
                     , 40 as incoming_stock
                     , 0 as demand_u
                     , 664 as plan_units
               union all
               select
                       'a' as material_code
                     , 10 as id
                     , 0 as actual_stock
                     , 90 as incoming_stock
                     , 0 as demand_u
                     , 295 as plan_units
               union all
               select
                       'a' as material_code
                     , 11 as id
                     , 0 as actual_stock
                     , 100 as incoming_stock
                     , 0 as demand_u
                     , 225 as plan_units
               union all
               select
                       'a' as material_code
                     , 12 as id
                     , 0 as actual_stock
                     , 30 as incoming_stock
                     , 0 as demand_u
                     , 276 as plan_units
               union all
               select
                       'a' as material_code
                     , 13 as id
                     , 0 as actual_stock
                     , 500 as incoming_stock
                     , 0 as demand_u
                     , 413 as plan_units
               )
select
        material_code
      , id
      , actual_stock
      , incoming_stock
      , demand_u
      , plan_units
      , case when id <= 7 then 0
             when id - 7 = 1 then lag(actual_stock, 1) over (partition by material_code order by id)
                     + incoming_stock
                     - lag(plan_units, 1) over (partition by material_code order by id)
             else (case when lag(final_stock, 1) over (partition by material_code order by id)
                     - lag(plan_units, 1) over (partition by material_code order by id) < 0 then incoming_stock
                        else lag(final_stock, 1) over (partition by material_code order by id)
                                - lag(plan_units, 1) over (partition by material_code order by id) + incoming_stock
                   end)
        end as stock_forecast
      , (actual_stock + stock_forecast) as final_stock
from tab