Calculating column based on filter on another table

[lg_analyst]

Trying to simplify my model as much as possible:

PRODUCTS table has a list of product_ids (which can be replicated), a season and a start and end date for the season

ORDERS a list of orders, product_ids purchased, quantity and order_date

DATE MASTER a calendar of the current and past year

Ther is a One to Many relationship between DATE MASTER(Date) and ORDERS(order_date)

On the PRODUCTS table I have this calculation: 

Where 

Max Filtered Date = MAX('DATE MASTER'[Date])

I'd like prod_quantity to be dynamic and change when a different date is selected from the date filter. In my example I set 31/07/2022 as max date and I'd like prod_quantity to return 3. I also tried creating a metric instead of the column but still can't get this to work. Is there a way to solve this? 

Output:

ORDERS table:

Thank you very much

[amitchandak]

@lg_analyst , No you can not use slicer value in a calculated column. You need to create a measure .

Move this calculation inside the expression part of Sumx , minx or countx

Sumx(Order, <Your code>)

[lg_analyst]

Hi @amitchandak thank you for replying. I can't use the formula you suggest because, as you can see from the first screenshot, I'm summing data in one table (PRODUCTS) but based on filters from that and another table (ORDERS). Can you suggest a way to work around this? 

Thank you