Column that Makes Calculations based on Rows from Previous Months and Specific Conditions

Hi tl1234 ,

Please refer below step 4 explaination.

I am calculating a cumulative monthly logic per ProNum–ACReg, but constrained by a "cap" (based on fiscal quarter). and also using a row-by-row accumulation approach.

Step 4: Group by ProNum & ACReg and Process Rows

Grouped = Table.Group(WithIndex, {"ProNum", "ACReg"}, {

It will group the data by ProNum and ACReg. For each group (each ProNum–ACReg pair), we perform custom row-by-row logic.
WithIndex is assumed to be a prior step where an Index column is added to ensure row identity.

{"AllRows", each

It Define the name of the grouped column "AllRows". The transformation is defined in the function that follows "each".

let
rows = [Index],

It reference the Index column for later filtering. rows here is just the list of Indexes within the group.This will help to avoid processing the same row more than once during accumulation.

sorted = Table.Sort(_, {"month", Order.Ascending}),

It sort the rows in each group by month ascending so that earliest month comes first.

result = List.Accumulate(
sorted[month],
{0, {}}, // {running total, result list}

It accumulating over the list of months from the sorted table. 0 is the initial running total of ac_iks_mth. {} is an empty list of processed rows.

(state, currentMonth) =>

This is the accumulator function. state holds {running_total, processed_rows_list}. currentMonth is the current month being processed.

let
currentRow = List.First(
Table.SelectRows(
sorted,
each [month] = currentMonth and not List.Contains(state{1}, [Index])
)
),

It will Select the first unprocessed row for the current month. state{1} contains already processed rows' Index. Ensures each row is only processed once, even if multiple rows have the same month.

cap = if Date.Month(currentRow[month]) <= 3 then currentRow[ac_oy_iks] else currentRow[ac_ny_iks],

It will Pick the appropriate cap value. If month ≤ 3 (Q1), use ac_oy_iks. Otherwise, use ac_ny_iks.

remCap = cap - state{0},

It Compute remaining capacity (remCap) after subtracting the running total.

iks_mth =
if remCap <= 0 then 0
else if currentRow[ac_total_cost] <= remCap then currentRow[ac_total_cost]
else remCap,

It compute this row's ac_iks_mth. If cap is already used up -> 0. If cost is within remaining cap -> use full cost. Otherwise ->only partial cap is usable.

newTotal = state{0} + iks_mth,

It will update running total by adding this month’s iks_mth.

newRow = Record.AddField(currentRow, "ac_iks_mth", iks_mth),

It will add the calculated iks_mth to the current row.

newState = {newTotal, state{1} & {currentRow[Index]}}
in
{newState{0}, newState{1} & {newRow}}

NewTotal is the updated sum. Add the current row’s Index to processed list. Add the newRow (with ac_iks_mth) to the result list.

){1}

It is the result of List.Accumulate is a state {sum, list}.The second element ({1}), the list of processed rows with the new column.

in
Table.FromRecords(result)

It will convert the result list of records back into a table.

}),

Combined = Table.Combine(Grouped[AllRows])

It Combined all grouped tables (one per ProNum–ACReg) into a single table again.

As you mentioned that  you don't want to start with  blank query.  you would  like to start with two other merged queries. Please refer below M code.

let

Source = Table.NestedJoin(
ac_total_cost_only_est_month, {"ProNum", "ACReg"},
ac_est_total, {"ProNum", "ACReg"},
"ac_est_total", JoinKind.LeftOuter
),

#"Expanded ac_est_total" = Table.ExpandTableColumn(Source, "ac_est_total", {"ac_oy_iks", "ac_ny_iks"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded ac_est_total", {"ProNum", "ACReg", "ac_oy_iks", "ac_ny_iks", "month", "ac_total_cost"}),
ChangedTypes = Table.TransformColumnTypes(#"Reordered Columns", {
{"ProNum", type text},
{"ACReg", type text},
{"ac_oy_iks", type number},
{"ac_ny_iks", type number},
{"month", type date},
{"ac_total_cost", type number}
}),

AddCap = Table.AddColumn(ChangedTypes, "cap_value", each
if Date.Month([month]) <= 3 then [ac_oy_iks] else [ac_ny_iks], type number),

Sorted = Table.Sort(AddCap, {{"ProNum", Order.Ascending}, {"ACReg", Order.Ascending}, {"month", Order.Ascending}}),

Grouped = Table.Group(Sorted, {"ProNum", "ACReg"}, {
{"AllRows", each
let
rows = Table.Sort(_, {"month", Order.Ascending}),
rowList = Table.ToRecords(rows),
result = List.Accumulate(
rowList,
[sum = 0, output = {}],
(state, current) =>
let
cap = if Date.Month(current[month]) <= 3 then current[ac_oy_iks] else current[ac_ny_iks],
remCap = cap - state[sum],
iks_mth = if remCap <= 0 then 0 else if current[ac_total_cost] <= remCap then current[ac_total_cost] else remCap,
updated = Record.AddField(current, "ac_iks_mth", iks_mth)
in
[sum = state[sum] + iks_mth, output = state[output] & {updated}]
)[output]
in
Table.FromRecords(result)
}
}),

Combined = Table.Combine(Grouped[AllRows]),

Final = Table.Sort(Combined, {{"ProNum", Order.Ascending}, {"ACReg", Order.Ascending}, {"month", Order.Ascending}}),

#"Filtered Rows" = Table.SelectRows(Final, each ([ProNum] = "00425"))
in
#"Filtered Rows"

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Hello v-dineshya thanks for the extra detail. The below code seems to have worked.

Sorry to be a pain but I'll need to use similar formulas for a lot of the data I'm working with and I still don't fully understand what you did. This is the part I don't understand:

If you could please explain it in extreme detail in words someone who knows nothing about coding and/or power bi would understand it would be super helpful and very much appreciated. 

Also, the way the code is written by power bi when you're creating steps vs how you wrote it is different. You can see this in the first two images I attached. Just wondering why this is/if it matters.

Thanks!