Multiple Yearly FX conversions and whether to have larger fact tables or use DAX

[Robert_Kopp]

I have a question for the community.

I have a fact table of 22M rows and currently 9 rows (7 Dimension Keys, 1 Amount field, 1 Date Field).  

The amount field is in local currency and my end-users want to be able to view the amounts in USD but with a variety of conversion rates based on yearly FXs (5 FXs for each currency). 

The 2 approaches that I came up with are below. 

1. Join the FX rate for each year to the Fact table in Power Query. 

    Calculate out an additional column in my fact table for each yearly FX

    Remove the FX rate columns as my last step in power query

    Create a small table which would perform the selection of which FX year they want and a DAX which does the Sum of the right column in my fact table. 

2. Build a currency table which has each FX in it with columns for Year and Currency Code

   Write my DAX expression to perform the FX conversion for each currency and provide the overall sum as a result

I'm not sure which method is going to have better performance or if there is another method that I'm not seeing. Method 1 is simple DAX but does add 5 additional decmial fields to my Fact table. Method 2 is a slimmer fact table but the DAX needs to carry more of the weight. 

Let me know your thoughts

[lbendlin]

In our industry we use monthly FX rates.  So we have a reference table by currency and yearmonth that we then tie into our data model as needed.  Would that be a possible approach for you? Or are your cutovers happening in random intervals?