Connect Two Tables and Remove Transactions after date.

[Anonymous]

Hello!

I have two inventory tables

• INVWORK contains the qty and cost of all receipts, and a column that represents how many of each receipt has been sold. 
• INVDETAIL contains detail on sales and adjustments.

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!

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien

[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... ?