# Currency conversion DAX



## Alex_N (May 14, 2014)

Hello,
I've been struggling with something that should be easy for the experienced users...


I have two tables. Fact table with transactions in USD and Exchange rates. I need users to be able to dynamically do currency conversion by selecting a desired output currency via slicer. When they select EUR, they see the amount in EUR (calculated using the rates for the selected currency and respective month) etc…
Many transactions in one month, many currencies (and their rates) in one month also. I am not sure how to build the many to many model...
Many thanks in advance,


Transactions

Month
Transact_ID
Amount_USD
201310
123456
10
201310
135802
15
201311
149382
25
201311
164320
8
201311
180752
90
201312
198827
110
201312
218710
20


<tbody>

</tbody> 

Rates

Curr_Code
Period
Rate
EUR
201310
1.3533
GBP
201310
1.619
USD
201310
1
EUR
201311
1.3595
GBP
201311
1.606
USD
201311
1
EUR
201312
1.3595
GBP
201312
1.628
USD
201312
1


<tbody>

</tbody>


----------



## kurtheric (May 14, 2014)

Good morning.  To join the tables, you might want to try creating a "Junction" table.  Create a table with just a list of dates....1/1/2014....1/2/2014....etc.  You should then be able to connect your two existing tables to the Junction table without error.  In addition, it sounds like your data is going to present further questions.  Generally when working with currencies, don't focus upon just months, but be specific about the day of capture.  Eventually someone is going to have a question about if your calculations are based upon the Balance Sheet (BS) Rate or Income Statement (IS) Rate.  It's nice to have the option of answering both options.  Hope this helps.


----------



## RightMeow (May 14, 2014)

Since I cannot attach the workbook I created, here is a description of what I did. Create the following three tables in the PowerPivot window: 





[DimDate] table with one field named [Period], that for the sake of this example, has the following values: 201310, 201311, and 201312. In practice, you may wish to have daily dates (as mentioned above by kutheric). 


[FactTransactions] table with the following fields: [Transact_ID], [Amount_USD], and [Period]. Data for this table is from your original posting.


[DimFXRate] table with the following fields: [Curr_Code], [Period], and [Rate]. Data for this table is from your original posting.

 

After creating these tables, create a relationship between the following columns: 



DimDate[Period] and FactTransactions[Period];

DimDate[Period] and DimFXRate[Period]



Define the following measures: 



[US Dollar Amount]:=SUM(FactTransactions[Amount_USD]) on the [FactTransactions] table.


[Exchange Rate]:=IF([US Dollar Amount],SUM(DimFXRate[Rate]),BLANK())]) on the [DimFXRate] table. 


[Foreign Currency Amount]:= [US Dollar Amount]* [Exchange Rate] on the [DimFXRate] table. I assume that to calculate the foreign currency amount, we multiply. If you need to divide by the exchange rate, simply change the “*” to a “/”. 



**********************************************************

**********************************************************



Now build the pivot table as follows. Use the following as row labels in the following order: 



DimDate[Period] field;

FactTransactions[Transact_ID] field (optional if you want to look at individual transactions);

DimFXRate[Curr_Code]



Use the following in the Values portion of the pivot table in the following order: 



[Exchange Rate] measure

[US Dollar Amount] measure;

[Foreign Currency Amount] measure



Now create slicers based on the DimFXRate[Curr_Code] field. This will get you what you need (as least it did for me).


----------



## Alex_N (May 15, 2014)

Hello RightMeow, thanks for the efforts to help! Really appreciate your time spend helping me!

Actually putting the Currencies in the pivot is not a bad idea, I will consider this option.
Your solution is good but it requires that we have in the pivot the months and also the totals are showing wrong values as all the amount in USD is divided to the sum of the rates for all months…
My model is more complex (my mistake I should of stated that originally, will know for future) - I have a lot of Amount_USD (different cost types) each of these for different customers.
So the pivot could be and will be prepared on customers and we will not have the months in the pivot which will compromise the calculations….
I am looking for a solution like this one, but hopefully more elegant and well performing:

[Curr_conv_MoM]:=SUMX(FactTransactions,FactTransactions[Amount_USD] /CALCULATE(SUM(DimFXRates[Rate]), FILTER(DimDate,CALCULATE(COUNTROWS(FactTransactions)>0))))

In this way I have the different periods applied regardless of how I play with the pivot. It just need to be enhanced with HASONEVALUE or IF so we can avoid the multiple slicers selection to give meaningless data. 1 major problem:
-          Slow performance when deployed by VisualStudio2010 on Analysis services (2012)… (I guess as SUMX is performing on a row level) I cannot make it work with Summarize no matter how I tried… This link is very useful for that by the way, but I was not able to reach meaningful results….

Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering | SQLBI

The Flat conversion (using only one rate) rate can be done very elegantly using non related tables. Source: DAX Formulas for PowerPivot, chapter 12 – Disconnected tables
Thank you again, appreciate your help!!!


----------



## Alex_N (May 21, 2014)

I think I found the elegant solution I was looking for...

Currency_conversion:= SUMX ('DimDate',[Sum of Amount_USD]/[MinRate])

where MinRate:= MIN([Rate])     - a measure created in DimFXRate table that in case of released slicer keeps the lowest rate applied.


----------

