How can I filter the quantity sold in one fact table based on the expiry date in another fact table

[Landcrab]

Hi there,

I have two fact tables:

-Products Shipped

-Products Sold

And 3 dimension tables:

-Item 

-Customer 

-Calendar

 

The Products Shipped table has two date columns, Shipped Date and Expiry Date where the Shipped Date is in a 1:M relationship with the date column in the Calendar table, and the Expiry Date is not linked to any other tables.

Both fact tables have the columns item number, customer number and a date column, that have been set up with 1:M relationship between them and this works as expected.

The 1:M relationship between the dimension (Calendar Date) and the fact table dates (Ship Date and Date Sold) work as expected.

Now, I can create a visual in Power BI where the Calendar Date filters both the Shipped Quantity and the Sold Quantity based on these relationshipsbetween the tables, and in addition to this, I need it be filter down the Sold Quantity even further to only include Quantities Sold where the where the Expiry Date >= to the Calendar Date, something like this:

Calculate (SUM(Quantity Sold), Filter(Product Shipped, Product Shipped[Expiry Date] >= MIN(Calendar[Date]) )

However this does not work as there is no direct relationship between the fact tables and the Expiry Date, and,  Expiry Date is in one fact table and the Quantity Sold is in the other fact table that dont have a direct link between the fact tables..

I have tried creating a relationship between the Expiry Date and Calendar Date and merge the two fact tables together to achieve this, but so far no luck.. 

Any ideas how this could be achieved please?

Thank you.

[amitchandak]

@Landcrab , In such a case I prefer to bring the data into the same table. Now either based date sold and date shipped and item or Item and batch we need to copy information from one table to another

New column in product sold

Maxx(filter('product shipped', 'product shipped[Item] = ' product sold'[item] && 'product shipped[batch] = ' product sold'[batch]), 'product shipped'[Expiry Date])

or

//based on order

Maxx(filter('product shipped', 'product shipped[Item] = ' product sold'[item] && 'product shipped[order] = ' product sold'[order]), 'product shipped'[Expiry Date])