Converting to Foreign Currencies

Converting to Foreign Currencies

I have a FORECAST table that has Qty, Product & Customer
A Product Table that has Product, Price in CURRENCY
An Exchange Rate Table with Currency & Date

I created a Measure Foreign Sales = Forecast Qty x Product Sell Price.
But when I try to convert foreign sales into NZD I get errors.

Despite watching image numerous times the formulae still gives errors.

Probably beginners error but I am stumped

PBIX as attached


A few things I noticed, (always tricky stuff this currency conversion)
your sales table does not specify the currency
your data table is not marked as datetable
your sales table does not include the date mentioned in the exchange rate table
your lookupvalue formula should look something like this:
Total NZD Sales =
SUMX(‘Sales 2019’;
‘Sales 2019’[SALES]
LOOKUPVALUE(‘Exchange Rates’[RATE];
‘Exchange Rates’[DATE];‘Sales 2019’[DATE];
‘Exchange Rates’[CURR];‘Sales 2019’[currency type]))


1 Like

Thanks for posting your question @Alexcel . To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

@Alexcel This would be a design choice on your part depending on how your customers order, but in the models we’ve built we don’t have customers changing currencies for ordering, so we would make ordering currency an attribute of the customer lookup table, not Sales 2019 or Forecast. Might help keep your fact table thin by not adding an additional field to what I assume would ultimately be your biggest tables.

Hi @Alexcel, we’ve noticed that no response has been received from you since the 19th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!