Power BI exchange rate calculation

Question

I am having a Sales transaction table with RateDate,OrderAmount and Transcation Currency

and also I am having exchange rate with BaseCurrency,targetCurrency and Rate

I need to convert Sales amount to Base Currency and I have tried the following DAX equation

AmountUSD = SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date],Fact_SalesOrder[RateDate],CurrencyRates[Base Currency],"USD",CurrencyRates[Target Currency],Fact_SalesOrder[Currency]))

But I am not getting a proper result How can I solve this? 

Answer 1

Use the following two measures

Selected Currency = VALUES('ReportingCurrencies'[Currency])

 And

SalesAmount = SWITCH(TRUE(),
[Selected Currency] = "EUR",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"EUR",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)),
[Selected Currency] = "USD",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"USD",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)),
[Selected Currency]

Want to learn more about Power BI? Check out today: Power BI course