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

[tl1234]

Hello,

Apologies in advance because it was very hard for me to write out what I'm looking for, but hopefully the sample below with the values I need filled in will help. The formula I'm trying to work out is for ac_iks_mth and it needs to be in Power BI M language in the query transformation section as all data manipulations in the dashboard I'm working on need to be traceable (ps sorry if wrong terminoloy - I'm new to Power BI).

The value I need for this new column is sort of a running total/difference grouped by ProNum, ACReg, and month.

But to caclulcate the value needed, the formula must compare ac_total_cost to either ac_oy_iks or ac_ny_iks as well as any other previous rows for the same ProNum/ACReg combination. If the month is January, February, or March it needs to look at ac_oy_iks and if it is any other month then it needs to look at ac_ny_iks. Further, the total for all rows per ACReg/ProNum cannot be greater than the unique value in either either ac_oy_iks or ac_ny_iks (the one that applies by month) and if it is greater than that value then 0 needs to be entered in ac_iks_mth. If you look at ProNum 00425 ACReg 050-01 you'll see what I mean here - it has $83037.00 for each row but that is the unique value for the ProNum/ACReg combination that each value in the new column needs to be based on in conjunction with the value in the previous ac_iks_mth row for the same ProNum/ACReg combination.

I've tried looking into and testing a bunch of options but keep having issues getting it to do the right thing. Any help would be appreciated! 🙂

ProNum	ACReg	ac_oy_iks	ac_ny_iks	month	ac_total_cost	ac_iks_mth
00125	132-03	$0.	$2,180.00	Saturday, February 1, 2025	$1,234.00	$0.00
00125	132-03	$0.	$2,180.00	Tuesday, July 1, 2025	$4,360.00	$2,180.00
00125	955-03	$0.	$16,600.00	Tuesday, July 1, 2025	$24,900.00	$16,600.00
00125	966-03	$0.	$7,920.00	Tuesday, July 1, 2025	$23,760.00	$7,920.00
00225	772-02	$0.	$0.00	Friday, August 1, 2025	$108,480.00	$0.00
00325	120-03	$0.	$6,650.00	Tuesday, April 1, 2025	$3,800.00	$3,800.00
00325	120-03	$0.	$6,650.00	Tuesday, July 1, 2025	$11,400.00	$2,850.00
00325	132-03	$0.	$12,426.00	Tuesday, July 1, 2025	$26,160.00	$12,426.00
00425	050-01	$0.	$83,037.00	Tuesday, April 1, 2025	$116,625.00	$83,037.00
00425	050-01	$0.	$83,037.00	Thursday, May 1, 2025	$10,885.00	$0.00
00425	050-01	$0.	$83,037.00	Sunday, June 1, 2025	$18,660.00	$0.00

[v-dineshya · Accepted solution]

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.

[Nasif_Azam]

Hey @tl1234 ,

It seems like you're trying to create a calculated column in Power BI M language that computes a running total based on the ac_total_cost and either ac_oy_iks or ac_ny_iks, depending on the month, while ensuring that the total does not exceed the unique value for each ProNum/ACReg combination. Try the below M Code:

let
    // Your existing data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9b8IwEIb/ShRlvKK782dGFoZKneiGGFI1apEQQgYP/PsGO9hxGEKX6Ib3fXR+LrtdjUisaqhJ8BuKYWhwdf8ykMUV4jBvu6t3390Nqk3/5XznbhVBxRh6DQELGYNNKOzhJeqn7y8B+u6PBVCC0GMm56fQVqkCSho0LlFZQvsITRoFV+uCa6DlRawAk7ZNhUjlEDFmUMCZOoY37hCAa//jL9dCKFqQD0tTpSIqZSy2HJ6i5luuz+5wnDIF2PT4NP+XOn87EUjMl7LqmTo7PzFI1ktKNVBSmhuRK0MG1bAtZa4VgMIsSaD72VmN5Nx5mfzrXUR/dKUIBGvV88GWkVt/Gh2c+oJoQevyF9j/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProNum = _t, ACReg = _t, ac_oy_iks = _t, ac_ny_iks = _t, month = _t, ac_total_cost = _t, ac_iks_mth = _t]),

    // Step 1: Change column types to appropriate data types
    ChangedType = Table.TransformColumnTypes(Source, {
        {"month", type date}, 
        {"ac_total_cost", type number}, 
        {"ac_oy_iks", type number}, 
        {"ac_ny_iks", type number}, 
        {"ac_iks_mth", type number} // Ensure that ac_iks_mth is treated as a number
    }),

    // Step 2: Rename existing ac_iks_mth column to avoid conflict
    RenameExistingColumn = Table.RenameColumns(ChangedType, {{"ac_iks_mth", "ac_iks_mth_old"}}),

    // Step 3: Add previous month's value for each ProNum and ACReg combination
    AddPrevRow = Table.AddColumn(RenameExistingColumn, "Prev_ac_iks_mth", each 
        let
            // Filter rows with the same ProNum and ACReg
            FilteredRows = Table.SelectRows(RenameExistingColumn, (r) => r[ProNum] = [ProNum] and r[ACReg] = [ACReg]),
            // Sort the rows by month to get the previous one
            SortedRows = Table.Sort(FilteredRows, {"month", Order.Ascending}),
            // Get the current month's date
            CurrentMonth = [month],
            // Get the previous row based on the month
            PreviousRows = Table.SelectRows(SortedRows, (r) => r[month] < CurrentMonth),
            PrevRow = if Table.RowCount(PreviousRows) > 0 then 
                Record.Field(Table.Last(PreviousRows), "ac_iks_mth_old")
            else 
                0
        in 
            PrevRow),

    // Step 4: Calculate the value for ac_iks_mth considering the specified logic
    AddAcIksMth = Table.AddColumn(AddPrevRow, "ac_iks_mth", each 
        let
            // Ensure that the 'ac_oy_iks' and 'ac_ny_iks' columns are treated as numbers
            IksValue = if List.Contains({1, 2, 3}, Date.Month([month])) then [ac_oy_iks] else [ac_ny_iks],
            // Calculate running total based on the previous row's value and current total cost
            RunningTotal = [Prev_ac_iks_mth] + [ac_total_cost],
            // Ensure the total does not exceed the selected ac_iks value
            FinalValue = if RunningTotal > IksValue then 0 else RunningTotal
        in 
            FinalValue)
in
    AddAcIksMth

 

Your Data:

The Final Result:

The Power BI File: Rows from Previous Months.pbix

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[tl1234]

@Nasif_Azam thanks for getting back to me. I included the column called ac_iks_mth only as an example of what I need. That column shows what I need this formula to do and is the column I'm trying to add.

[v-dineshya]

Hi @tl1234 ,

Thank you for reaching out to the Microsoft Fabric Community forum.

Please follow below steps.

1.  I took blank query and placed below M code in Power Query editor. 

let
Source = Table.FromRows({
{"00125","132-03", 0, 2180, #date(2025,2,1), 1234},
{"00125","132-03", 0, 2180, #date(2025,7,1), 4360},
{"00125","955-03", 0, 16600, #date(2025,7,1), 24900},
{"00125","966-03", 0, 7920, #date(2025,7,1), 23760},
{"00225","772-02", 0, 0, #date(2025,8,1), 108480},
{"00325","120-03", 0, 6650, #date(2025,4,1), 3800},
{"00325","120-03", 0, 6650, #date(2025,7,1), 11400},
{"00325","132-03", 0, 12426, #date(2025,7,1), 26160},
{"00425","050-01", 0, 83037, #date(2025,4,1), 116625},
{"00425","050-01", 0, 83037, #date(2025,5,1), 10885},
{"00425","050-01", 0, 83037, #date(2025,6,1), 18660}
},
{"ProNum", "ACReg", "ac_oy_iks", "ac_ny_iks", "month", "ac_total_cost"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"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"

2.  For testing the Code, i took sample inputs,  "ProNum"=00425 and "ACReg"=050-01, it has "83037" for each row but that is the unique value for the ProNum/ACReg combination that each value in the new column needs to be based on in conjunction with the value in the previous ac_iks_mth row for the same ProNum/ACReg combination.

3. Please refer output snap and PBIX file for your reference.

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.

[tl1234]

Hello @v-dineshya , thanks for this! Can you please explain what the steps mean so I understand what's going on? Also, it doesn't seem to be in the right format for where I need to put it which is either here:

or here:

It looks like it could potentially work but since I'm not sure what the code is doing or where to put it I can't test right now.

Thanks!

[v-dineshya]

Hi @tl1234 ,

You are trying to create a calculated column (ac_iks_mth) using M code in Power BI Query Editor not in DAX, based on cumulative totals grouped by ProNum and ACReg, with logic depending on the month.

Please follow below steps.

1.  In Power Query editor, New source --> Blank query

2. Click on "Blank query" and at the top click on "Advanced editor" and delete everything in "advanced editor".

3.  Paste the below M code in "Advanced editor".

let
Source = Table.FromRows({
{"00125","132-03", 0, 2180, #date(2025,2,1), 1234},
{"00125","132-03", 0, 2180, #date(2025,7,1), 4360},
{"00125","955-03", 0, 16600, #date(2025,7,1), 24900},
{"00125","966-03", 0, 7920, #date(2025,7,1), 23760},
{"00225","772-02", 0, 0, #date(2025,8,1), 108480},
{"00325","120-03", 0, 6650, #date(2025,4,1), 3800},
{"00325","120-03", 0, 6650, #date(2025,7,1), 11400},
{"00325","132-03", 0, 12426, #date(2025,7,1), 26160},
{"00425","050-01", 0, 83037, #date(2025,4,1), 116625},
{"00425","050-01", 0, 83037, #date(2025,5,1), 10885},
{"00425","050-01", 0, 83037, #date(2025,6,1), 18660}
},
{"ProNum", "ACReg", "ac_oy_iks", "ac_ny_iks", "month", "ac_total_cost"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"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"

4. And click OK and it will show the below output snap.

5.  the below steps explains the step y step process.

Step1 : This code will import the sample data

let
Source = Table.FromRows({
{"00125","132-03", 0, 2180, #date(2025,2,1), 1234},
{"00125","132-03", 0, 2180, #date(2025,7,1), 4360},
{"00125","955-03", 0, 16600, #date(2025,7,1), 24900},
{"00125","966-03", 0, 7920, #date(2025,7,1), 23760},
{"00225","772-02", 0, 0, #date(2025,8,1), 108480},
{"00325","120-03", 0, 6650, #date(2025,4,1), 3800},
{"00325","120-03", 0, 6650, #date(2025,7,1), 11400},
{"00325","132-03", 0, 12426, #date(2025,7,1), 26160},
{"00425","050-01", 0, 83037, #date(2025,4,1), 116625},
{"00425","050-01", 0, 83037, #date(2025,5,1), 10885},
{"00425","050-01", 0, 83037, #date(2025,6,1), 18660}
},
{"ProNum", "ACReg", "ac_oy_iks", "ac_ny_iks", "month", "ac_total_cost"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"ProNum", type text},
{"ACReg", type text},
{"ac_oy_iks", type number},
{"ac_ny_iks", type number},
{"month", type date},
{"ac_total_cost", type number}
})
in
ChangedTypes

Step 2: Add Cap Column

Add a new column cap_value based on the month logic:

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

Step 3: Sort and Index for Ordering

Sorted = Table.Sort(AddCap, {"ProNum", Order.Ascending}, {"ACReg", Order.Ascending}, {"month", Order.Ascending}),
WithIndex = Table.AddIndexColumn(Sorted, "Index", 0, 1, Int64.Type)

Step 4: Group by ProNum & ACReg and Process Rows

We will now group and use a custom function to simulate row-by-row cumulative logic.

Grouped = Table.Group(WithIndex, {"ProNum", "ACReg"}, {
{"AllRows", each
let
rows = [Index],
sorted = Table.Sort(_, {"month", Order.Ascending}),
result = List.Accumulate(
sorted[month],
{0, {}}, // {running total, result list}
(state, currentMonth) =>
let
currentRow = List.First(Table.SelectRows(sorted, each [month] = currentMonth and not List.Contains(state{1}, [Index]))),
cap = if Date.Month(currentRow[month]) <= 3 then currentRow[ac_oy_iks] else currentRow[ac_ny_iks],
remCap = cap - state{0},
iks_mth = if remCap <= 0 then 0 else if currentRow[ac_total_cost] <= remCap then currentRow[ac_total_cost] else remCap,
newTotal = state{0} + iks_mth,
newRow = Record.AddField(currentRow, "ac_iks_mth", iks_mth),
newState = {newTotal, state{1} & {currentRow[Index]}}
in
{newState{0}, newState{1} & {newRow}}
){1}
in
Table.FromRecords(result)
}
}),
Combined = Table.Combine(Grouped[AllRows])

Step 5: Final Cleanup

You may want to reorder or remove the Index, and sort the data again:

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

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.

[tl1234]

Thanks @v-dineshya. 

Can you please explain all the parts of step 4 please?

Also, I don't want to start with a blank query. I'd like to start with two other queries I merged. These ones:

[v-dineshya · Accepted solution]

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.

[tl1234 · Accepted solution]

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!

[v-dineshya]

Hi @tl1234 ,

In the explanation above, I have provided a step-by-step breakdown of the M code. For foundational understanding, please refer to the attached official Microsoft documentation. It offers comprehensive insights into the basics of M code, which will facilitate a clearer understanding of the implementation discussed.

Power Query M formula language reference - PowerQuery M | Microsoft Learn

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.

[v-dineshya]

Hi @tl1234 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.