Creating relationship between two tables

[Penn]

Hi Everyone,

I have two tables, sales table and price table as below.

Sales Table

Sales ID	Item ID	Invoice Date
SO0001	IT0002	30/04/2018
SO0002	IT0002	30/05/2019

Price Table

Item ID	From Date	End Date	Price
IT0002	01/01/1900	15/04/2018	$100.00
IT0002	16/04/2018	15/05/2018	$101.00
IT0002	16/05/2018	01/01/1900	$102.00

The relationship on Item ID will be many to many between these two tables. Noted that the dafult setting for null date is 01/01/1900.

Is there any way that I can do in Power BI to get the correct price ($102.00) for Sales ID SO0002? Thanks

[parry2k · Accepted solution]

@Penn try following calculated columns, In expression, change table and column name as per your data model.

Price = 
VAR itemid = Table4[Item ID]
VAR invdate = Table4[Invoice Date]
RETURN 
CALCULATE( 
    MAX( Table3[Price] ), 
    Table3[Item ID] = itemid,
    invdate >= Table3[From Date] ,
    invdate <= IF( YEAR( Table3[End Date] ) = 1900, invdate, Table3[End Date]) )

[Penn]

Hi @parry2k 

You are absolutely a legend! Never thought that I can do this in just one step. Learned something new today. Thanks

[parry2k]

@Penn glad to help.