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

Enterprise%20DNA%20Expert%20-%20Small


#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

Enterprise%20DNA%20Expert%20-%20Small