Lookupvalue date challenge

[norway84]

Hi,

I have two tables which have no relations:

• Table 1 with my sales transactions. The relevant columns are:
  • SKU_ID
  • DATE
• Table 2 with historic price changes
  • SKU_ID
  • DATE_PRICE_CHANGE 
  • PRICE

In Table 1 I would like a calculated columns with the relevant price. I would like to retrive the "PRICE" for the relevant SKU_ID where DATE_PRICE_CHANGE is the highest possible but still lower than DATE in TABLE 1.

So I imagine it would be something like the formula below where I need help with "xxxxxxxxx"-part

PRICE =
LOOKUPVALUE (
    Table2[Price];
    Table2[SKU_Id]; Table1[SKU_id];
    Table2[DATE_PRICE_CHANGE]; xxxxxxxxxxxxxxxxxx...............Table1[DATE]
)

Thank you in advance for any help. If "lookupvalue" isn't the best solution for this I'm happy for other suggestions as well.

Best regards,

[v-chuncz-msft · Accepted solution]

@norway84,

You may refer to the following example.

http://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228