Converting currency


#1

Hi Sam,
It has been a while since you last showcased currency conversion.

I needed to calculate revenue amounts showing in $, £ and € and convert to €. Initially I applied calculated columns in the sales data, which obviously had an adverse impact on the speed, but I wanted to validate the results on record level. I used lookupvalue to find the exchangerate ( from a table showing monthyear and the rate). Then I put this in a measure, again using lookupvalue and this worked great and dramatically improved the speed. Then I simulated with treatas, given your video on the use of treatas, and this worked fine too. So, which one is better, lookupvalue or treatas?
DAX used:
EUR Income =
SUMX( COMPLETED_DATASET;
COMPLETED_DATASET[Income] *
LOOKUPVALUE( Exchangerates[Rate];
Exchangerates[Month-Year]; COMPLETED_DATASET[CURmonthsyears];
Exchangerates[Currency]; COMPLETED_DATASET[Curr]) )

Exchange rate = CALCULATE(AVERAGE(Exchangerates[Rate]);TREATAS(VALUES(‘7 DateTable’[Month-Year]);Exchangerates[Month-Year]))

Paul


#2

Paul,

I believe that either function will work for you.

The mail difference being the LOOKUPVALUE required a relationship between the tables and TREATAS uses a virtual relationship.

Guy


#3

Guy,

Thanks.
What about performance, would treatas be better than lookupvalue in a sumx formula?

Paul


#4

Paul,

From what I have read I think that they will be about the same for performance.

Guy