Convert Currency

[GFAlexander]

Hi All,

I'm fairly new to Power BI and I've managed to find answers to most of what I've needed so far, I'm a little stuck on dealing with currencies though.  I have looked at other solutions in here, but I can't find anything that quite matches what I need, so your help would be appreciated.

I have tables and fields as follows:

Transaction_lines

   BI Amt  This is a transaction value but the vaue is in the currency of the transaction

   Transaction_ID  This links the transaction lines to the header (see below)

Transactions

   Transaction_ID  Links the header to the lines

   Currency_ID  As it sounds, identifies the currency

CurrencyRates

   Currency_ID

   Exchange_Rate  The exchange rate for the date

   Date_Effective   Effective date of the rate

What I want to be able to do is create a column or a measure (but I think it has to be a column) on the transaction_lines table that converts BI Amt by the exchange rate, but preferably by the latest rate available.  I'm struggling with this because I only know the currency_ID from the transactions table.

The joins exist between each of the tables I've listed.  Let me know if you need more information, I've tried to include everything that's relevant.

Thanks in advance for your help.

Gary

[v-jiascu-msft]

Hi Gary,

Please check out the demo here. The solution could be as below.

Column =
VAR maxDate =
    MAX ( 'CurrencyRates'[Date_Effective] )
RETURN
    Transaction_lines[BI Amt]
        * CALCULATE (
            MAX ( 'CurrencyRates'[Exchange_Rate] ),
            'CurrencyRates'[Date_Effective] = maxDate
        )

Best Regards,

Dale