datedynamicpowerbi-desktopcurrency-exchange-rates

Power BI exchange rate calculation

I am a newbie in Power BI desktop. We have a Sales transaction table with RateDate, orderAmount and transaction Currency

enter image description here

Also, we have an exchange rate table with BaseCurrency(standard currency i.e. USD,EUR and INR) ,target currency, effective date and Rate.

enter image description here

For analysis, We need to convert the Salesamount in any of the Base Currency i.e USD/INR/EUR as per the rate date-effective date mapping on the fly. How can I achieve that?

I tried following DAX code,

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 it results in 'infinity' value.

enter image description here

Solution

  • Finally got it :)

    Just need 2 measures: SalesAmount for Sales and Selected currency for selection.

    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] = "INR",SUMX(Fact_SalesOrder,[OrderAmount]/lookupvalue(CurrencyRates[Rate],CurrencyRates[Effective Date].[Date],Fact_SalesOrder[RateDate].[Date],CurrencyRates[Base Currency],"INR",CurrencyRates[Target Currency],Fact_SalesOrder[Currency],1)))