Look up fx rate across table using both row and column intelligence

[NordicS3130]

Hello,  I am a new user.  I have a table of data with items that I have purchased at different times.  The items have a quantity and a price but the price is in the local currency where they were purchased.  I would like to convert all of the costs to one currency (in this case EUR).  I also have a fx currency look up table which has the historical fx rates for many currencies going back many years.  I would like a calculated column which looks up the rate needed to convert the cost of the items to EUR on the date which I purchased them.   So for example, for item A, we will need to FX rate for 8/1/2024 and it will be for the USD column.  However, I want this to be dymanic as I will have many currenties in the table and do not want seperate fx tables for each conversion rate.

Thank you so much for your help!

Here is some sample data below.  These are in power bi as two different tables

[Anonymous]

Hi, @NordicS3130 

Based on your information, I create sample tables:

Then create a calculated column and try the following DAX expression:

EUR Cost = 
VAR PurchaseDate = 'Purchase Table'[Date]
VAR PurchaseCurrency = 'Purchase Table'[Price Currency]
VAR FXRate = 
    SWITCH(
        PurchaseCurrency,
        "USD", LOOKUPVALUE('FX Rate Table'[USD], 'FX Rate Table'[Date], PurchaseDate),
        "GBP", LOOKUPVALUE('FX Rate Table'[GBP], 'FX Rate Table'[Date], PurchaseDate),
        "CHF", LOOKUPVALUE('FX Rate Table'[CHF], 'FX Rate Table'[Date], PurchaseDate),
        "CNY", LOOKUPVALUE('FX Rate Table'[CNY], 'FX Rate Table'[Date], PurchaseDate),
        "INR", LOOKUPVALUE('FX Rate Table'[INR], 'FX Rate Table'[Date], PurchaseDate),
        "NOK", LOOKUPVALUE('FX Rate Table'[NOK], 'FX Rate Table'[Date], PurchaseDate),
        "SEK", LOOKUPVALUE('FX Rate Table'[SEK], 'FX Rate Table'[Date], PurchaseDate),
        "DKK", LOOKUPVALUE('FX Rate Table'[DKK], 'FX Rate Table'[Date], PurchaseDate),
        "JPY", LOOKUPVALUE('FX Rate Table'[JPY], 'FX Rate Table'[Date], PurchaseDate),
        "KRW", LOOKUPVALUE('FX Rate Table'[KRW], 'FX Rate Table'[Date], PurchaseDate),
        "THB", LOOKUPVALUE('FX Rate Table'[THB], 'FX Rate Table'[Date], PurchaseDate),
        "CHF", LOOKUPVALUE('FX Rate Table'[CHF], 'FX Rate Table'[Date], PurchaseDate),
        BLANK()
    )
RETURN
'Purchase Table'[Cost] * FXRate

Here is my preview

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NordicS3130]

Thank you very much.  That should work.  Is there any way to do it without having to specify each fx look up- as in to narrow the columns by currency and then just find the date?  This way if I need to add other FX rates I do not need to adjust the code

[Anonymous · Accepted solution]

Hi, @NordicS3130 

You can start by going to PowerQuery and unpivoting the exchange rate column

Then select Close and Apply. Create calculated column:

EUR Cost = 
VAR PurchaseDate = 'Purchase Table'[Date]
VAR PurchaseCurrency = 'Purchase Table'[Price Currency]
VAR FXRate = CALCULATE(SUM('Fx Rate Table'[Value]),FILTER('Fx Rate Table','Fx Rate Table'[Date]='Purchase Table'[Date]&&'Fx Rate Table'[Attribute]='Purchase Table'[Price Currency]))
RETURN
'Purchase Table'[Cost] * FXRate

If you want to change the data or add data in the future, you can also add it directly to PowerQuery, so that the structure of the current exchange rate table will not be destroyed, and when the data is added or changed, the calculation column will also be dynamically changed.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NordicS3130]

Hello,  Or perhaps does anyone have other suggestions?  I believe that I need to have a calculated column as I will create other complex calculations based on this data.  This is only a step towards the final result.

[NordicS3130]

Hello- no I do not want a slicer.  I want a calculated column which looks up the currency in the FX table based on the currency and the date.  

[dharmendars007]

Hello @NordicS3130 , 

This post should help you with currency conversion...

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-currency-exchange-based-on-currency-select...

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN