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

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.

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