Forum Discussion
Generate Moving Closing Balance Monthly in m-Query
Hi,
I'm looking for a solution to implement the below table in m-Query in Power BI:
| FileDate | Index | Opening Balance | Quantity | Consumption | AvailableStock | Closing Balance |
| 01-Jan-24 | 0 | 100 | 10 | -20 | 110 | 90 |
| 01-Feb-24 | 1 | 90 | 20 | -25 | 110 | 85 |
| 01-Mar-24 | 2 | 85 | 15 | -17 | 100 | 83 |
| 01-Apr-24 | 3 | 83 | 10 | -12 | 93 | 81 |
Working:
1. The Opening Balance for Jan 2024 is fixed = 100
2. Available Stock = Opening Balance + Quantity
3. Closing Balance = Available Stock + Consumption
4. The Closing Balance for each month becomes the Opening Balance for the next month
I would appreciate any pointers in this implementation.
Best Regads,
Kartik
let ob_start = 100, Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], s = ((rows) => List.Generate( () => [i = 0, r = rows{0}, ob = ob_start, av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[i] < List.Count(rows), (x) => [i = x[i] + 1, r = rows{i}, ob = x[cl_bal], av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[r] & {x[i], x[ob], x[av_stock], x[cl_bal]} ))(Table.ToList(Source, each _)), z = Table.FromList(s, each _, Table.ColumnNames(Source) & {"Index", "Opening Balance", "Available Stock", "Closing Balance"}) in z
4 Replies
let ob_start = 100, Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], s = ((rows) => List.Generate( () => [i = 0, r = rows{0}, ob = ob_start, av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[i] < List.Count(rows), (x) => [i = x[i] + 1, r = rows{i}, ob = x[cl_bal], av_stock = ob + r{1}, cl_bal = av_stock + r{2}], (x) => x[r] & {x[i], x[ob], x[av_stock], x[cl_bal]} ))(Table.ToList(Source, each _)), z = Table.FromList(s, each _, Table.ColumnNames(Source) & {"Index", "Opening Balance", "Available Stock", "Closing Balance"}) in zHi iktsharma ,
Thank you for choosing the Microsoft fabric Community Forum!
Thank you AlienSx for the helpful response.
Upon reviewing the provided information, below steps might assist you in the getting the expected output.- After loading the data,In Power Query Editor, go to the Home tab.
- Click on Advanced Editor.
- Replace it with the following M Query :
let
Source = Table.FromRecords({
[FileDate=#date(2024,1,1), Index=0, Quantity=10, Consumption=-20],
[FileDate=#date(2024,2,1), Index=1, Quantity=20, Consumption=-25],
[FileDate=#date(2024,3,1), Index=2, Quantity=15, Consumption=-17],
[FileDate=#date(2024,4,1), Index=3, Quantity=10, Consumption=-12]
}),
// Sorting by Index for correct calculation
SortedTable = Table.Sort(Source, {{"Index", Order.Ascending}}),// Opening Balance
OpeningBalanceInitial = 100,// Convert table into a list
TableList = Table.ToRecords(SortedTable),// List.Accumulate to calculate balances iteratively
ComputedList = List.Accumulate(
TableList,
{OpeningBalanceInitial, {}},
(state, current) =>
let
PreviousClosingBalance = state{0},
OpeningBalance = PreviousClosingBalance,
AvailableStock = OpeningBalance + current[Quantity],
ClosingBalance = AvailableStock + current[Consumption],
NewRecord = [
FileDate = current[FileDate],
Index = current[Index],
OpeningBalance = OpeningBalance,
Quantity = current[Quantity],
Consumption = current[Consumption],
AvailableStock = AvailableStock,
ClosingBalance = ClosingBalance
]
in
{ClosingBalance, state{1} & {NewRecord}}
){1}, // Extract computed records// Convert the computed list back to table
ResultTable = Table.FromRecords(ComputedList)
in
ResultTable
Refer the attached file and the screenshot for detailed understanding.If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Regards,
Pallavi.
