Forum Discussion
Currency conversion
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
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
6 Replies
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?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.
for referencing other table you should use RELATED
https://msdn.microsoft.com/en-us/query-bi/dax/related-function-dax
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
