i have inventory data need to calculate opeing closing to create detail ledger ItemDocdateQTYTYPEdocidBP Monitor19-03-2024150RCPTdo1BP Monitor17-04-202410ISSUdo2BP Monitor01-07-2024150RCPTdo3BP Monitor03-07-202410ISSUdo4BP Monitor04-07-2024300RCPTdo5BP Monitor02-12-2024100ISSUdo6BP Monitor27-12-2024250RCPTdo7BP Monitor21-01-2025250RCPTdo8BP Monitor19-02-2025150RCPTdo9BP Monitor13-03-2025100RCPTdo10BP Monitor15-04-2025250RCPTdo11BP Monitor30-04-2025200ISSUdo12BP Monitor05-07-2025300ISSUdo13BP Monitor21-08-2025200RCPTdo14EXPECTED OUTPUT1.Detail Ledger how to create this give measure

Helper III

1 year 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
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

Anonymous

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

16 Replies

MohamedFowzan1

Super User

1 year ago

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.

pankajnamekar25
Super User
1 year ago
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
  Helper III
  11 months ago
  If i select any between date its not show correct value
  state ment report
  if i select between date
  ledger
  
  statement

MohamedFowzan1

Super User

1 year 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
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

danextian

Super User

1 year ago

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]

rajasekaro

Helper III

1 year ago

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

Shahid12523
Community Champion
1 year ago
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 ✅

Anonymous
Not applicable
11 months ago
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.
Anonymous
Not applicable
11 months ago
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.
Anonymous
Not applicable
11 months ago
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.
Anonymous
Not applicable
11 months ago
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.

Forum Discussion

Inventory opening closing

16 Replies

Related content

Dax Queries for Open Close stock

opening closing

Inventory opening ,closing quantity

Closing and opening inventory calculation

Stock opening closing calculation