The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello!
I have two inventory tables
The INVWORK 'QTYSOLD' column populates even if the detail transaction occurs after a specific date.
I need to figure out how to pull the sum of receipts and sales in the WORK table, but omit any sales/adjustments after the specific date.
If I were to read the WORK table, the qty balance would be zero as of 3/31/2021. Yet, I need to ignore the sale on 4/15/21 in the DETAIL table, and the actual quantity on 3/31/2021 should be 1.
Any help on how to filter/maxx etc for a measure would be awesome. This report is being built as a historical stock status report as of any date.
INVWORK
INVDETAIL:
thank you!
Hi @Anonymous ,
Did you solve this? If not, post the following information, it looks like an easily solvable and common scenario.
1) Do you have a calendar table with its Date field connected to the date fields of INVWORK and INVDETAIL tables' date column?
2) What is the native structure of INVWORK and INVDETAIL and what are the calculated columns in these tables? QTYRECVD, QTYSOLD, UNITCOST etc... calculated columns based on other tables?
3) In the INVWORK table, is there an opening stock column? How are you calculating the inventory on a specific date without an opening stock? Is it just the difference between (Sum of Receipts up to the considered date) - (Sum of quantities sold up to that considered date)?
4) In your INVWORK table, for a particular item, you are maintaining the Receipts and Quantity sold against each date. Is it that you want to calculate? Or the cumulative receipts and sales up to that date for all dates?
5) Basically, it is not very clear what your table structure is? what do you want to calculate? you want to calculate "calculated columns" or "measures" etc... ?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |