How to sumx with predicate in another table?

[Anonymous]

Hi all, please help me with this case

Scenario, I have a sales table. I need to create a column Forecast Quantity base on the sales quantity in the sales table and put it in Forecast table.

Target store_ID and target product_ID is in the forecast table (don't need to calculate for all transaction)

In SQL it would be something like this 

SELECT SUM(QUANTITY) 
FROM W_SELLOUT_TRANSACTION_F  AS A
LEFT JOIN W_OPTIMAL_INVENTORY_F AS B ON
A.W_STORE_WID = B.W_STORE_WID
AND A.W_PRODUCT_WID = B.W_PRODUCT_WID
WHERE A.W_DATE_WID < 20191231
AND A.W_DATE_WID >= 20190101)

What I have in mind is like this:

Sell Out Qty = SUMX(V_W_SELLOUT_TRANSACTION_F,
CALCULATE(sum(V_W_SELLOUT_TRANSACTION_F[QUANTITY]),
V_W_OPTIMAL_INVENTORY_F[W_STORE_WID]=V_W_SELLOUT_TRANSACTION_F[W_STORE_WID],
V_W_OPTIMAL_INVENTORY_F[W_PRODUCT_WID]=V_W_SELLOUT_TRANSACTION_F[W_PRODUCT_WID],
V_W_SELLOUT_TRANSACTION_F[W_DATE_WID]<V_W_OPTIMAL_INVENTORY_F[FROM_DATE_WID]))

[amitchandak]

create a concatenated field on W_STORE_WID and W_PRODUCT_WID and join tables W_SELLOUT_TRANSACTION_F W_OPTIMAL_INVENTORY_F
using that field.
Also, create a date dimension and join that with W_DATE_WID
Now you use date slicer or calculation in the formula to control your logic

[Anonymous]

Hi @amitchandak , The data is very big (25M rows transaction data) and I already try an SQL solution. I want to find a DAX equivalent solution.