Opening Qty calculation

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.

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