Opening Qty calculation

[rajasekaro]

STOCKVALUEID	BRANCHID	PROJID	LOCID	ITEMID	STOCKTYPE	DOCDATE	VALUE	IOTYPE	QTY
10000001	2000001	3000001	4000002	5000001	OPST	01-01-2024	100	P	10
10000002	2000001	3000001	4000002	5000002	TRNS	01-01-2024	5487	P	30
10000003	2000001	3000001	4000002	5000003	OPST	01-01-2024	8753	P	56
10000004	2000001	3000001	4000002	5000004	TRNS	01-01-2024	7658	P	65
10000005	2000001	3000001	4000002	5000005	OPST	01-01-2024	6584	P	45
10000006	2000001	3000001	4000002	5000006	TRNS	01-01-2024	9867	P	30
10000007	2000004	3000006	4000001	5000001	TRNS	01-01-2024	4544	P	12
10000008	2000004	3000006	4000001	5000002	OPST	01-01-2024	665	P	23
10000009	2000004	3000006	4000001	5000006	OPST	01-01-2024	4566	P	34
10000010	2000001	3000002	4000003	5000001	OPST	23-03-2024	6537	P	57
10000011	2000001	3000002	4000003	5000002	TRNS	20-03-1900	9686	P	76
10000012	2000001	3000002	4000003	5000003	OPST	25-03-2024	4564	P	80
10000013	2000001	3000002	4000003	5000004	TRNS	26-03-2024	7566	P	70
10000014	2000001	3000002	4000003	5000005	OPST	27-03-2024	3444	P	60
10000015	2000001	3000002	4000003	5000006	TRNS	28-03-2024	5344	P	50
10000016	2000001	3000002	4000003	5000002	TRNS	29-03-2024	5433	P	40
10000017	2000001	3000002	4000003	5000002	TRNS	30-03-2024	4545	P	30
10000018	2000001	3000001	4000002	5000001	TRNS	02-01-2024	100	P	10
10000019	2000001	3000001	4000002	5000002	TRNS	02-01-2024	5487	m	30
10000020	2000004	3000006	4000001	5000003	TRNS	02-01-2024	8753	p	56
10000021	2000004	3000006	4000001	5000004	TRNS	02-01-2024	7658	m	65
10000022	2000004	3000006	4000001	5000005	TRNS	02-01-2024	6584	p	45
10000023	2000001	3000002	4000003	5000006	TRNS	02-01-2024	9867	p	30
10000024	2000001	3000002	4000003	5000003	TRNS	02-01-2024	4544	p	12
10000025	2000001	3000002	4000003	5000004	TRNS	02-01-2024	665	p	23
10000026	2000001	3000002	4000003	5000005	TRNS	02-01-2024	4566	m	34
10000027	2000001	3000002	4000003	5000006	TRNS	02-01-2024	6537	m	57
10000028	2000001	3000002	4000003	5000005	TRNS	02-01-2024	9686	m	76
10000029	2000001	3000002	4000003	5000006	TRNS	02-01-2024	4564	m	80

I need to calculate the following measures:

• Opening Qty:
  All records where iotype = "p" and stocktype = "OPST" before the selected date range.

• Received Qty:
  Records where iotype = "p" and stocktype <> "OPST" within the selected date range.

• Issued Qty:
  Records where iotype = "m" within the selected date range.

• Closing Qty:
  Opening Qty + Received Qty - Issued Qty
  
  I tried but not working

[v-ssriganesh · Accepted solution]

Hello @rajasekaro,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using the sample data you provided, and I was able to get the expected output for Opening Qty, Received Qty, Issued Qty, and Closing Qty measures as per your requirements.

For your reference, here are the details:

Date Range Tested:

01-01-2024 to 02-01-2024

Results:

• Opening Qty: (Blank) – This occurs because there are no records dated before 01-01-2024. If you prefer to display 0 instead of blank, you can wrap your measure with COALESCE.
• Received Qty: 313
• Issued Qty: 342
• Closing Qty: –29

These outputs match your expected calculation:

Closing Qty = Opening Qty + Received Qty – Issued Qty

Closing Qty = 0 + 313 – 342 = –29

For your convenience, I am attaching the .pbix file containing the sample data, date table, and measures so you can review or adapt it as needed.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hello @rajasekaro,
Hope everything’s going great on your end! Just checking in has the issue been resolved, or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.

[v-ssriganesh]

Hello @rajasekaro,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.

[danextian · Accepted solution]

Hi @rajasekaro 

Ensure that you have a dates table that has a single direction one-to-many relationship to your fact table.

Create these measures:

Opening Qty = 
CALCULATE (
    SUM ( Data[QTY] ),
    FILTER ( ALL ( DatesTable ), DatesTable[Date] < MIN ( DatesTable[Date] ) ),
    KEEPFILTERS ( Data[IOTYPE] = "p" && Data[STOCKTYPE] = "OPST" )
)


Received Qty = 
CALCULATE (
    SUM ( Data[QTY] ),
    KEEPFILTERS ( Data[IOTYPE] = "p" && Data[STOCKTYPE] <> "OPST" )
)

Issued Qty = 
CALCULATE (
    SUM ( Data[QTY] ),
    KEEPFILTERS ( Data[IOTYPE] = "m" )
)

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

The results above are based on the logic provided but it is confusing. Sholdn't the previous day's clsoing be the current day's opening balance? Would have been better if you provided your expected results. 

Please see the attached pbix

[v-ssriganesh · Accepted solution]

Hello @rajasekaro,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using the sample data you provided, and I was able to get the expected output for Opening Qty, Received Qty, Issued Qty, and Closing Qty measures as per your requirements.

For your reference, here are the details:

Date Range Tested:

01-01-2024 to 02-01-2024

Results:

• Opening Qty: (Blank) – This occurs because there are no records dated before 01-01-2024. If you prefer to display 0 instead of blank, you can wrap your measure with COALESCE.
• Received Qty: 313
• Issued Qty: 342
• Closing Qty: –29

These outputs match your expected calculation:

Closing Qty = Opening Qty + Received Qty – Issued Qty

Closing Qty = 0 + 313 – 342 = –29

For your convenience, I am attaching the .pbix file containing the sample data, date table, and measures so you can review or adapt it as needed.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hello @rajasekaro,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[burakkaragoz]

Hi @rajasekaro ,

Thanks for sharing the details — your logic for calculating the quantities looks good conceptually. Here's how you might define those measures in DAX, assuming your table is named StockTransactions:

Opening Qty =
CALCULATE(
    SUM(StockTransactions[QTY]),
    StockTransactions[VALUEIOPETYPE] = "P",
    StockTransactions[STOCKTYPE] = "OPST",
    StockTransactions[DOCDATE] < MIN('DateTable'[Date])
)

Received Qty =
CALCULATE(
    SUM(StockTransactions[QTY]),
    StockTransactions[VALUEIOPETYPE] = "P",
    StockTransactions[STOCKTYPE] <> "OPST",
    StockTransactions[DOCDATE] >= MIN('DateTable'[Date]) &&
    StockTransactions[DOCDATE] <= MAX('DateTable'[Date])
)

Issued Qty =
CALCULATE(
    SUM(StockTransactions[QTY]),
    StockTransactions[VALUEIOPETYPE] = "M",
    StockTransactions[DOCDATE] >= MIN('DateTable'[Date]) &&
    StockTransactions[DOCDATE] <= MAX('DateTable'[Date])
)

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

Make sure your DOCDATE column is properly related to your DateTable, and that you're using a slicer or filter context from that table — otherwise the date filters won’t apply correctly.

Let me know if you're using a different column for filtering or if the QTY field is not numeric — that could also cause issues.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

Translation and text formatting supported by AI assistance

[bhanu_gautam]

@rajasekaro Try using

DAX
Opening Qty =
CALCULATE(
SUM('Table'[QTY]),
'Table'[IOTYPE] = "P",
'Table'[STOCKTYPE] = "OPST",
'Table'[DOCDATE] < MIN('Date'[Date])
)

DAX
Received Qty =
CALCULATE(
SUM('Table'[QTY]),
'Table'[IOTYPE] = "P",
'Table'[STOCKTYPE] <> "OPST",
'Table'[DOCDATE] >= MIN('Date'[Date]),
'Table'[DOCDATE] <= MAX('Date'[Date])
)

DAX
Issued Qty =
CALCULATE(
SUM('Table'[QTY]),
'Table'[IOTYPE] = "M",
'Table'[DOCDATE] >= MIN('Date'[Date]),
'Table'[DOCDATE] <= MAX('Date'[Date])
)

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

[rajasekaro]

Opening Qty  not working 

[bhanu_gautam]

@rajasekaro , dax
Opening Qty =
CALCULATE(
SUM(stockvalue[qty]),
stockvalue[IOTYPE] = "P",
stockvalue[stocktype] = "OPST",
stockvalue[DOCDATE] < MIN('calender'[Date])
)

try using this if it does not work try changing some filter values