Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to aggregate from 1 table using predicate in another table?

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  AND A.W_DATE_WID <= 20191231  AND A.W_DATE_WID >= 20190101

 

What I have in mind is like this:

Sell Out Qty Last Week = 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]))

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Based on your information, we can create a calculated column using following DAX to requirement.

     

    Sell Out Qty Last Week =
SUMX (
    FILTER (
        'V_W_SELLOUT_TRANSACTION_F',
        WEEKNUM ( 'V_W_SELLOUT_TRANSACTION_F'[W_DATE_WID] ) = 'V_W_OPTIMAL_INVENTORY_F'[W_Week]
            && 'V_W_SELLOUT_TRANSACTION_F'[W_PRODUCT_WID] = 'V_W_OPTIMAL_INVENTORY_F'[W_PRODUCT_WID]
            && 'V_W_SELLOUT_TRANSACTION_F'[W_STORE_WID] = [W_STORE_WID]
    ),
    [QUANTITY]
)

     

     


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Based on your information, we can create a calculated column using following DAX to requirement.

     

    Sell Out Qty Last Week =
SUMX (
    FILTER (
        'V_W_SELLOUT_TRANSACTION_F',
        WEEKNUM ( 'V_W_SELLOUT_TRANSACTION_F'[W_DATE_WID] ) = 'V_W_OPTIMAL_INVENTORY_F'[W_Week]
            && 'V_W_SELLOUT_TRANSACTION_F'[W_PRODUCT_WID] = 'V_W_OPTIMAL_INVENTORY_F'[W_PRODUCT_WID]
            && 'V_W_SELLOUT_TRANSACTION_F'[W_STORE_WID] = [W_STORE_WID]
    ),
    [QUANTITY]
)

     

     


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.