Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
1 year ago
Solved

Opening Qty calculation

STOCKVALUEIDBRANCHIDPROJIDLOCIDITEMIDSTOCKTYPEDOCDATEVALUEIOTYPEQTY
100000012000001300000140000025000001OPST01-01-2024100P10
100000022000001300000140000025000002TRNS01-01-20245487P30
100000032000001300000140000025000003OPST01-01-20248753P56
100000042000001300000140000025000004TRNS01-01-20247658P65
100000052000001300000140000025000005OPST01-01-20246584P45
100000062000001300000140000025000006TRNS01-01-20249867P30
100000072000004300000640000015000001TRNS01-01-20244544P12
100000082000004300000640000015000002OPST01-01-2024665P23
100000092000004300000640000015000006OPST01-01-20244566P34
100000102000001300000240000035000001OPST23-03-20246537P57
100000112000001300000240000035000002TRNS20-03-19009686P76
100000122000001300000240000035000003OPST25-03-20244564P80
100000132000001300000240000035000004TRNS26-03-20247566P70
100000142000001300000240000035000005OPST27-03-20243444P60
100000152000001300000240000035000006TRNS28-03-20245344P50
100000162000001300000240000035000002TRNS29-03-20245433P40
100000172000001300000240000035000002TRNS30-03-20244545P30
100000182000001300000140000025000001TRNS02-01-2024100P10
100000192000001300000140000025000002TRNS02-01-20245487m30
100000202000004300000640000015000003TRNS02-01-20248753p56
100000212000004300000640000015000004TRNS02-01-20247658m65
100000222000004300000640000015000005TRNS02-01-20246584p45
100000232000001300000240000035000006TRNS02-01-20249867p30
100000242000001300000240000035000003TRNS02-01-20244544p12
100000252000001300000240000035000004TRNS02-01-2024665p23
100000262000001300000240000035000005TRNS02-01-20244566m34
100000272000001300000240000035000006TRNS02-01-20246537m57
100000282000001300000240000035000005TRNS02-01-20249686m76
100000292000001300000240000035000006TRNS02-01-20244564m80

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

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

9 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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]

     

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        1 year ago

        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

  • 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

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    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.

    • v-ssriganesh's avatar
      v-ssriganesh
      Community Support
      1 year ago

      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.

  • danextian's avatar
    danextian
    Super User
    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

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.