Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
1 year ago
Solved

Opening Qty calculation

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 4...
Need Help
Tips and Tricks
Tutorial Requests
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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.

  • danextian's avatar
    danextian
    1 year ago

    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

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

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