Stock opening closing calculation

rajasekaro

DOCDATE	ITEMID	QTY	PLUSORMINUS	BRANCHID	LOCID	STOCKVALUE
24-11-2024	ITEM001	35	p	BRANCH3	LOC2	1247.4
16-09-2024	ITEM010	25	m	BRANCH3	LOC2	1422.41
26-07-2024	ITEM011	64	m	BRANCH3	LOC3	2774.7
16-07-2024	ITEM009	52	m	BRANCH3	LOC1	1488.85
07-06-2024	ITEM008	91	p	BRANCH1	LOC1	3458.4
18-02-2025	ITEM020	44	m	BRANCH1	LOC5	3906.58
06-07-2024	ITEM012	18	m	BRANCH2	LOC5	180.85

i have this data
how to calculate opening qty, resived qty ,issue qty,Closing qty

v-pnaroju-msft

Thankyou, @Akash_Varuna, for your response.

Hi rajasekaro,

We appreciate your question on the Microsoft Fabric Community Forum.

Please follow these steps to solve the issue:

Load the data using Power BI’s "Enter Data" option or Excel.
Create a Date Table using the CALENDAR function and connect it to the DOCDATE field.
Use these measures:
a.Opening Qty: sums the quantity before the selected date using ALL().
b.Received Qty: includes only the entries with "p" values.
c.Issued Qty: includes only the entries with "m" values.
d.Closing Qty = Opening + Received – Issued.

This method will work smoothly with slicers and give correct, dynamic results for any date you select.

We have attached a screenshot and a sample pbix file for your reference:

If our answer helps you, please mark it as the accepted solution and give kudos. This will help other community members with similar questions.

If you have any more questions, please feel free to contact the Microsoft Fabric community.

Thank you.

Akash_Varuna

Hi @rajasekaro To calculate stock metrics please follow the below:

Opening Qty: Sum of all transactions before the selected date.
Received Qty: Sum of QTY where PLUSORMINUS = "p" for the selected date range.
Issued Qty: Sum of QTY where PLUSORMINUS = "m" for the selected date range.
Closing Qty: Calculated as [Opening Qty] + [Received Qty] - [Issued Qty].

rajasekaro

its not showing correct value if i select date filter

Helpful resources