Inventory opening closing

Hi rajasekaro ,

Let me give you the ready-to-use Power Query (M) solution that will works for you.

1. Sort the data by Docdate.
2. Create Received and Issued columns.
3. Calculate Running Closing balance.
4. Calculate Opening (previous closing, first row = 0).
5. Produce two outputs.
• Detail Ledger (row-wise)
• Summary Statement (Opening=0, total Received, total Issued, Closing).

use the bellow M code.

let
    // Replace with your source table name
    Source = YourTable,  
    // 1. Sort by Docdate
    Sorted = Table.Sort(Source, {{"Docdate", Order.Ascending}}),
    // 2. Add Received & Issued columns
    AddReceived = Table.AddColumn(Sorted, "Received", each if [TYPE] = "RCPT" then [QTY] else 0, type number),
    AddIssued   = Table.AddColumn(AddReceived, "Issued", each if [TYPE] = "ISSU" then [QTY] else 0, type number),
    // 3. Add Index column
    AddIndex = Table.AddIndexColumn(AddIssued, "Index", 1, 1, Int64.Type),
    // 4. Add Closing as running balance
    AddClosing = Table.AddColumn(AddIndex, "Closing", each
        List.Sum(List.FirstN(AddIndex[Received],[Index]))
        - List.Sum(List.FirstN(AddIndex[Issued],[Index]))
    , type number),
    // 5. Add Opening = Previous row Closing (first row = 0)
    AddOpening = Table.AddColumn(AddClosing, "Opening", each
        if [Index] = 1 then 0
        else AddClosing[Closing]{[Index]-2}
    , type number),
    // 6. Reorder columns
    DetailLedger = Table.ReorderColumns(AddOpening, {"Item","Docdate","Opening","Received","Issued","Closing"}),
    // 7. Create Summary Statement
    OpeningVal = 0,
    ReceivedVal = List.Sum(DetailLedger[Received]),
    IssuedVal = List.Sum(DetailLedger[Issued]),
    ClosingVal = OpeningVal + ReceivedVal - IssuedVal,
    Statement = #table({"Item","Docdate","Opening","Received","Issued","Closing"}, {{"Statement","",OpeningVal,ReceivedVal,IssuedVal,ClosingVal}})
in
[DetailLedger = DetailLedger, Statement = Statement]

If you still face any issues, let us know happt to help.

Thanks,
Akhil