How to create a lookup in order to create cross rates?

[jopezzo]

Hello everyone!

I have a table with exchange rates from Foreign currency to EUR.

What I would like to create is a formula/DAX expression in order to calculate cross-rates (for example USD/CHF) for a defined date.

Here is a simple example:

Let's say I have below table, with data until "Rate" column. I would like to retrieve the CHF rate of 01/01/2017 (0.88) in order to calculate the USD/CHF rate (I guess I would somehow need to use the "divide" and "filter" functions, but I don't know how). Any idea?

Thanks for your help!

Valid from	From	To	Rate	CHF	USD/CHF
1/01/2017	USD	EUR	0.84	0.88	=0.84/0.88
1/01/2017	CHF	EUR	0.88

[Anonymous · Accepted solution]

Hi @jopezzo,

You can add a calculate column and use date and currency name to find the specific value:

CHF = LOOKUPVALUE('Currency'[Rate],'Currency'[Valid from],[Valid from],'Currency'[From],"CHF") 

Then add a column to calculate the result.

USD/CHF = [Rate]/[CHF]

Regards,

Xiaoxin Sheng

[jopezzo]

Thanks a lot! That's really great!