Currency conversion

[adieball]

Hi

I'm actually trying to do this in Excel, but "M" is "M" 🙂

I have a table CurrencyRate that holds conversion rates per country to € per Year (a single rate per year) like:

I also have a "budget" Table that holds the budget values in local currency.

with "Country", "Value"  and the "CountryYear" custom column

What would be the "best" way to have the local currency values available in €?

• create a column in the "budget" table
• a dax measure?

How would I do the lookup?

thanks

[v-cherch-msft · Accepted solution]

Hi @adieball

You may also use LOOKUPVALUE Function (DAX). You may also go to ‘Edit Queries’ to merge two queries. Then add custom column as requested.

Regards,

Cherie

[Stachu]

is there a join between the tables?
I see few options possible
1) new column created in M (Power Query)
2) new column created in DAX (Power Pivot)
3) measure created using SUMX (Power Pivot)
I'd suggest going with option 1, 3 being second choice, as I expect worse performance

is there a join between CurrencyRate and budget or is that impossible due to different rates per year?

[adieball]

Hi

thaks for the quick reply.

in both tables I created the column "CountryYear" which I used to link the two.

Struggling to create the new column in one table as I'd need values from both from it but when I create a new column I can only choose from the current table.

[Stachu]

you need to use RELATED for that
https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax

[Stachu]

for referencing other table you should use RELATED
https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax

[adieball]

That would be a DAX measure instead of an additional column, right?