Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
11 months ago
Solved

Inventory opening closing

i have inventory data need to calculate opeing closing to create detail ledger  Item Docdate QTY TYPE docid BP Monitor 19-03-2024 150 RCPT do1 BP Monitor 17-04-2024 10 ISSU do2 ...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    10 months ago

    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

MohamedFowzan1's avatar
MohamedFowzan1
Super User
11 months ago

Hi rajasekaro 

I was able to create this (Make sure to sort the data by DocDate first):


After loading the data create the necessary calculated columns:

Closing = 'Table'[Opening] + 'Table'[Received] - 'Table'[Issue]

Issue = IF('Table'[TYPE] = "ISSU", 'Table'[QTY], 0)

Opening = 
VAR CurrentDate = 'Table'[Docdate]
VAR CurrentIndex = 
    RANKX(
        FILTER('Table', 'Table'[Item] = EARLIER('Table'[Item])),
        'Table'[Docdate],
        ,
        ASC,
        Dense
    )
RETURN
IF(
    CurrentIndex = 1,
    0,
    CALCULATE(
        SUM('Table'[Received]) - SUM('Table'[Issue]),
        FILTER(
            'Table',
            RANKX(
                FILTER('Table', 'Table'[Item] = EARLIER('Table'[Item])),
                'Table'[Docdate],
                ,
                ASC,
                Dense
            ) = CurrentIndex - 1
        )
    )
)

Received = IF('Table'[TYPE] = "RCPT", 'Table'[QTY], 0)


Final Output:


The above should work, if your data is huge and donot want to use EARLIER function,

In Powerquery,
Sort by DocDate Ascending
Add an Index Column starting from 1.

Add calculated columns:

Received and Issue as before.

Closing as before.

Add the Opening column using the below DAX:

Opening =
VAR CurrentIndex = 'Table'[Index]
RETURN
IF(
    CurrentIndex = 1,
    0,
    LOOKUPVALUE('Table'[Closing], 'Table'[Index], CurrentIndex - 1)
)


If not Columns, and you need to use measures:

Closing Measure = 
VAR CurrentDate = MAX('Table'[Docdate])
RETURN
    CALCULATE(
        SUMX(
            'Table',
            SWITCH(
                TRUE(),
                'Table'[TYPE] = "RCPT", 'Table'[QTY],
                'Table'[TYPE] = "ISSU", -'Table'[QTY],
                0
            )
        ),
        FILTER(ALL('Table'), 'Table'[Docdate] <= CurrentDate)
    )


Issue Measure = 
CALCULATE(
    SUM('Table'[QTY]),
    'Table'[TYPE] = "ISSU"
)

Opening Measure = 
VAR CurrentDate = MAX('Table'[Docdate])
VAR PreviousDate =
    CALCULATE(
        MAX('Table'[Docdate]),
        FILTER(ALL('Table'),'Table'[Docdate] < CurrentDate)
    )
RETURN
    IF(
        ISBLANK(PreviousDate),
        0,
        CALCULATE([Closing Measure], FILTER(ALL('Table'), 'Table'[Docdate] = PreviousDate))
    )

Received Measure = 
CALCULATE(
    SUM('Table'[QTY]),
    'Table'[TYPE] = "RCPT"
)


Output with measures:


Got rid of the blanks:
Use:

Issue Measure Measure = 
VAR Result =
    CALCULATE(
        SUM('Table'[QTY]),
        FILTER(
            'Table',
            'Table'[TYPE] = "ISSU" &&
            'Table'[Docdate] = MAX('Table'[Docdate])
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

Received Measure = 
VAR Result =
    CALCULATE(
        SUM('Table'[QTY]),
        FILTER(
            'Table',
            'Table'[TYPE] = "RCPT" &&
            'Table'[Docdate] = MAX('Table'[Docdate])
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

 

rajasekaro's avatar
rajasekaro
Helper III
11 months ago

MohamedFowzan1 

the statement report not showing correct value 
and if i show the closing qty in KPI its also showing wrong value how to fix this