Repeated Calculated Column Optimization

Hi NBU_FFF ,

The best approach to optimize your repeated Day0–Day9 calculated columns is to offload the recursive logic into Power Query using index-based row referencing. DAX is not well-suited for recursion over calculated columns because it lacks true looping behavior and cannot reference previously calculated column values in subsequent rows. Power Query, on the other hand, allows row-by-row processing using index columns and custom steps. You can start by sorting your table by [Line], [VS], and [MAV], then add an index column starting from 0. This index will serve as the “day” indicator, and you can reference the previous row's result for the recursive logic. After adding the index, you create a custom column such as [DayQty] using a recursive function. Here’s a simplified version of how the logic might look:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    SortedRows = Table.Sort(Source,{{"Line", Order.Ascending}, {"VS", Order.Ascending}, {"MAV", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(SortedRows, "Day", 0, 1, Int64.Type),
    AddDayQty = Table.AddColumn(AddIndex, "DayQty", each null, type number),
    RecursiveCalculation = List.Accumulate(
        {0..Table.RowCount(AddDayQty)-1},
        AddDayQty,
        (state, current) =>
            let
                row = state{current},
                prev = if current = 0 then null else state{current - 1},
                qty = row[Qty],
                cap = row[Capacity],
                endGroupQty = row[End_GroupQty_Previous],
                vsQty = row[VS_Qty],
                prevQty = if current = 0 then null else prev[DayQty],
                calc = if current = 0 then
                    qty - qty / (endGroupQty + vsQty) * cap
                else
                    (qty + prevQty) - (qty + prevQty) / (endGroupQty + vsQty) * cap,
                newState = Table.ReplaceRows(state, current, {Record.TransformFields(row, {"DayQty", each calc})})
            in
                newState
    )
in
    RecursiveCalculation

This logic processes the table row-by-row while calculating the DayQty recursively. The first row (Day0) uses your original formula, and all subsequent rows use the previous row’s DayQty to compute the current one. This way, you no longer need to manually create Day1 to Day9 columns in DAX. You end up with a single [DayQty] column that contains the recursive result for each day index, and the logic can be extended to as many days as needed without repetition.

Best regards,