Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
1 year ago
Solved

Stock opening closing calculation

DOCDATEITEMIDQTYPLUSORMINUSBRANCHIDLOCIDSTOCKVALUE
24-11-2024ITEM00135pBRANCH3LOC21247.4
16-09-2024ITEM01025mBRANCH3LOC21422.41
26-07-2024ITEM01164mBRANCH3LOC32774.7
16-07-2024ITEM00952mBRANCH3LOC11488.85
07-06-2024ITEM00891pBRANCH1LOC13458.4
18-02-2025ITEM02044mBRANCH1LOC53906.58
06-07-2024ITEM01218mBRANCH2LOC5180.85

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

Need Help
Tips and Tricks
Tutorial Requests
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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:

    1. Load the data using Power BI’s "Enter Data" option or Excel.
    2. Create a Date Table using the CALENDAR function and connect it to the DOCDATE field.
    3. 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.




7 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    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's avatar
      rajasekaro
      Helper III
      1 year ago

      its not showing correct value if i select date filter

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    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:

    1. Load the data using Power BI’s "Enter Data" option or Excel.
    2. Create a Date Table using the CALENDAR function and connect it to the DOCDATE field.
    3. 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.




  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi rajasekaro,

    We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

    If you find the response helpful, please mark it as the accepted solution, as this will help other members with similar queries.Should you have any further queries, kindly feel free to contact the Microsoft Fabric community

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi rajasekaro,

    We wanted to check with you because we have not received any reply. If your problem is solved, please share the solution with the community. This will help others who face the same issue.

    Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi rajasekaro,

    We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

    If our response was helpful, please mark it as the accepted solution, as this helps the broader community.

    Thank you.