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
AddAcIksMthYour 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
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.
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
