Generate Moving Closing Balance Monthly in m-Query

[iktsharma]

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

[v-pagayam-msft]

Hi @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.

[SamanthaPuaXY]

Hi @iktsharma , 

Which are the columns you will have? And the columns that needs to be generated?

[AlienSx · Accepted solution]

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

[iktsharma]

Thank You @AlienSx for responding. This helps!