SumProduct two tables with no relationship

[Anonymous]

I have two tables

QuantityTable
RefDate	Product	Quantity
31-Jan	A	10
31-Jan	B	20
31-Jan	C	30
28-Feb	B	100
28-Feb	C	200
28-Feb	D	300
31-Mar	A	50
31-Mar	D	70

PriceTable
EoMDate	Product	Price
31-Jan	A	2.2
31-Jan	B	3.2
31-Jan	C	4.2
31-Jan	D	5.2
28-Feb	A	6.2
28-Feb	B	7.2
28-Feb	C	8.2
28-Feb	D	9.2
31-Mar	A	10.2
31-Mar	B	11.2
31-Mar	C	12.2
31-Mar	D	13.2

These two tables have no relationship and are many to many with respect Date and Product

How does one calculate the following-->

SumX(QuantityTable[Quantity] * PriceTable[Price])

where QuantityTable[RefDate] == PriceTable[EomDate] && QuantityTable[Product] == PriceTable[Product]

In general I expect for all dates all products exist in PriceTable

[Anonymous]

Hi Jihwan_Kim, thank you for your reply.

In general you understanding is correct except

There is no 'unique' Product table and hence there is no join between Sales and Product Price and hence 'related'  does not work (actually Sales and Product are two Calculated Tables in a  measure with some appropriate filters), So lookup does not work

[philouduv]

Hello @Anonymous ,

If you want to avoid relationship in your model I managed to find a work around implying to recreate a table with the price definition with the other one
This solution will find the latest price even not on the same date:

Table 2 = PriceTable
table 3 = QuantityTable

First re_creation of EOM in table 3:

Then retrieving the associated price:

then for a final value per product:

Or sales per date:

Hope it helps

[Anonymous]

@philouduv thank you for your reply. 

Interesting that you managed to filter two tables without any relationship between them.

For me Filter(Table2, Table2[Product] == Table3[Product] && Table2[RefDate] >= Table3[EoMDate]) gives dax error

it does not show Table3 as a valid option to put on right side of the filter equation

[philouduv]

Hey @Anonymous ,

Please Verify that you create the column in the right result table (table 3)
On the view of the data select the table 3 before click on crete a new column

Best regards,

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Sales measure: =
SUMX (
    Sales,
    Sales[Quantity]
        * LOOKUPVALUE (
            Product_Price[Price],
            Product_Price[Product], RELATED ( 'Product'[Product] ),
            Product_Price[EoMDate], RELATED ( 'Calendar'[Date] )
        )
)