currency conversion with budgeted rates

[abfdjuk]

Hi.

I have two tables of data. One is the currency, year and exchange rate "ExchangeRates" the other is "Invoices" with colums for year, net amount and currency code. I have tried so many different formulas - all the ones I would use in excel don't seem to work! and keep hitting errors. The latest one I get is: a single value cannot be determined...

My question is how do I create a calculation which does does a currency conversion using the exchange rate in a set year from the "exchangeRates" table and then calculates this against the invoice amount?

many thanks for any help

[johnt75 · Accepted solution]

Converted amount =
SUMX (
    Invoices,
    Invoices[Net amount]
        * LOOKUPVALUE (
            ExchRate[Rate],
            ExchRate[Currency code], Invoices[Currency code],
            ExchRate[Year], Invoices[Year]
        )
)

[abfdjuk]

I just tried this and get the following error: Function 'LOOKUPVALUE' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[lukiz84]

yes, because YearCode in your table is of type "String" (text in PowerQuery) -> change it to integer (number in powerquery)

[abfdjuk]

nks for your quick response. Now I get this error: A table of multiple values was supplied where a single value was expected. Any ideas what I need to change?

[johnt75]

it looks like you have multiple entries for the same combination of year and currency code. 

[abfdjuk]

Thanks for your help.

[lukiz84]

ConvertedSum = 
   VAR InvoiceDate = SELECTEDVALUE(Invoices[InvoiceDate])
   VAR Currency = SELECTEDVALUE(Invoices[Curreny])
   VAR ConversionRate = 
      CALCULATE(
         MIN(ConversionRates[rate]),
         ConversionRates[currency] = Currency, 
         ConversionRates[cear] = YEAR(InvoiceDate)
      )

   RETURN SUM(Invoices[Invoices - Net amount]) * ConversionRate