Solved: Re: Inventory opening closing

rajasekaro · ‎09-02-2025

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
BP Monitor	01-07-2024	150	RCPT	do3
BP Monitor	03-07-2024	10	ISSU	do4
BP Monitor	04-07-2024	300	RCPT	do5
BP Monitor	02-12-2024	100	ISSU	do6
BP Monitor	27-12-2024	250	RCPT	do7
BP Monitor	21-01-2025	250	RCPT	do8
BP Monitor	19-02-2025	150	RCPT	do9
BP Monitor	13-03-2025	100	RCPT	do10
BP Monitor	15-04-2025	250	RCPT	do11
BP Monitor	30-04-2025	200	ISSU	do12
BP Monitor	05-07-2025	300	ISSU	do13
BP Monitor	21-08-2025	200	RCPT	do14

EXPECTED OUTPUT
1.Detail Ledger

how to create this give measure

v-agajavelly · ‎09-23-2025

Hi @rajasekaro ,

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

Sort the data by Docdate.
Create Received and Issued columns.
Calculate Running Closing balance.
Calculate Opening (previous closing, first row = 0).
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

View solution in original post

v-agajavelly · ‎09-21-2025

Hi @rajasekaro ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Thanks,
Akhil.

v-agajavelly · ‎09-14-2025

Hi @rajasekaro ,

Just circling back, did you get a chance to test any of the super users solutions? If one of them worked, please mark it as the accepted solution so it can help others in the community as well.

Regards,
Akhil.

v-agajavelly · ‎09-09-2025

Hi @rajasekaro ,

Is super users shared solutions were you able to try out any of these solutions yet? If you hit any roadblocks or something doesn’t behave as expected, feel free to share back so we can dive deeper.

Regards,
Akhil.

v-agajavelly · ‎09-03-2025

Hi @rajasekaro ,

Thanks to the @pankajnamekar25 , @MohamedFowzan1 and @danextian for sharing the detailed approaches .
@rajasekaro do you think the solutions shared above will work for your case? Please let us know if you run into anything that doesn’t work as expected, so we can help you further.

Regards,
Akhil.

danextian · ‎09-02-2025

Hi @rajasekaro

Create the following measures:

Sum of qty = 
SUM ( inventory[QTY] )

Opening =
CALCULATE (
    [Received] - [Issued],
    FILTER (
        ALL ( inventory[Docdate] ),
        inventory[Docdate] < MIN ( inventory[Docdate] )
    )
)

Received = 
CALCULATE ( [Sum of qty], KEEPFILTERS ( inventory[TYPE] = "RCPT" ) )

Issued = 
CALCULATE ( [Sum of qty], KEEPFILTERS ( inventory[TYPE] = "ISSU" ) )

Closing = 
CALCULATE (
    [Received] - [Issued],
    FILTER (
        ALL ( inventory[Docdate] ),
        inventory[Docdate] <= MAX ( inventory[Docdate] )
    )
)

or 

Closing =
[Opening] + [Received] - [Issued]

Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

rajasekaro · ‎09-02-2025

i have inventory data need to calculate opeing closing to create detail ledger
Table: STOCKVALUE

Item	Docdate	QTY	TYPE	docid
BP Monitor	19-03-2024	150	RCPT	do1
BP Monitor	17-04-2024	10	ISSU	do2
BP Monitor	01-07-2024	150	RCPT	do3
BP Monitor	03-07-2024	10	ISSU	do4
BP Monitor	04-07-2024	300	RCPT	do5
BP Monitor	02-12-2024	100	ISSU	do6
BP Monitor	27-12-2024	250	RCPT	do7
BP Monitor	21-01-2025	250	RCPT	do8
BP Monitor	19-02-2025	150	RCPT	do9
BP Monitor	13-03-2025	100	RCPT	do10
BP Monitor	15-04-2025	250	RCPT	do11
BP Monitor	30-04-2025	200	ISSU	do12
BP Monitor	05-07-2025	300	ISSU	do13
BP Monitor	21-08-2025	200	RCPT	do14

EXPECTED OUTPUT
1.Detail Ledger

2.Statement

mh2587 · ‎09-03-2025

You already posted same thread,
https://community.fabric.microsoft.com/t5/Desktop/Inventory-opening-closing/m-p/4814555#M1431892

Did I answer your question? If so, please mark my post as a solution!

Proud to be a Super User!

Muhammad Hasnain

kushanNa · ‎09-02-2025

Duplicate ? https://community.fabric.microsoft.com/t5/Desktop/Inventory-opening-closing/m-p/4814555#M1431892

Shahid12523 · ‎09-02-2025

Do it in Power Query (easiest):

1.Sort by Docdate.

2.Add columns:

Received = if TYPE="RCPT" then QTY else 0
Issued = if TYPE="ISSU" then QTY else 0

3.Add Closing = running sum of (Received – Issued).

4.Add Opening = Previous row Closing (shifted down, first = 0).

Result → Detail Ledger.

For Statement:

Opening = 0
Received = SUM(Received) = 1800
Issued = SUM(Issued) = 620
Closing = 1180 ✅

Shahed Shaikh

rajasekaro · ‎09-02-2025

NOT working

v-agajavelly · ‎09-23-2025

Hi @rajasekaro ,

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

Sort the data by Docdate.
Create Received and Issued columns.
Calculate Running Closing balance.
Calculate Opening (previous closing, first row = 0).
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 · ‎09-02-2025

Providing the final working 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 Measure = 
VAR Result =
    CALCULATE(
        SUM('Table'[QTY]),
        FILTER(
            'Table',
            'Table'[TYPE] = "ISSU" &&
            'Table'[Docdate] = MAX('Table'[Docdate])
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

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 = 
VAR Result =
    CALCULATE(
        SUM('Table'[QTY]),
        FILTER(
            'Table',
            'Table'[TYPE] = "RCPT" &&
            'Table'[Docdate] = MAX('Table'[Docdate])
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

Result:

For a detailed summary on the possibilities or you would like 0 instead of blanks please review my previous comment. For just the measures, please use these.

MohamedFowzan1 · ‎09-02-2025

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 · ‎09-04-2025

@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

pankajnamekar25 · ‎09-02-2025

Hello @rajasekaro

Try with below measures

Inward =

SUMX (

FILTER ( Inventory, Inventory[TYPE] = "RCPT" ),

Inventory[QTY]

)

Outward =

SUMX (

FILTER ( Inventory, Inventory[TYPE] = "ISSU" ),

Inventory[QTY]

)

Running Balance (Closing Stock)

Closing Stock =

VAR CurrentDate = MAX ( Inventory[DocDate] )

RETURN

CALCULATE (

SUMX (

Inventory,

IF ( Inventory[TYPE] = "RCPT", Inventory[QTY], -Inventory[QTY] )

),

FILTER ( Inventory, Inventory[DocDate] <= CurrentDate )

)

Opening Stock (Previous Closing)

Opening Stock =

VAR CurrentDate = MAX ( Inventory[DocDate] )

VAR PreviousClosing =

CALCULATE (

[Closing Stock],

FILTER ( Inventory, Inventory[DocDate] < CurrentDate )

)

RETURN

COALESCE ( PreviousClosing, 0 )

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:

LinkedIn

rajasekaro · ‎09-04-2025

If i select any between date its not show correct value

state ment report

if i select between date
ledger

statement

Inventory opening closing

Helpful resources

Power BI Dataviz World Championships

Join our Community Sticker Challenge 2026

Power BI Monthly Update - January 2026

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

Inventory opening closing

Helpful resources

Power BI Dataviz World Championships

Join our Community Sticker Challenge 2026

Power BI Monthly Update - January 2026

FabCon Atlanta 2026

How to Get Your Question Answered Quickly