Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

24.2. Currency Conversion Scenario (m:n)

A good many-to-many dimension financial scenario is the currency conversion scenario. Here the goal is to provide you with the ability to look at your sales figures in the desired currency. Let's start by reviewing the [Internet Sales Amount] for June 2004:

select {
 [Measures].[Internet Sales Amount]
} on columns, non empty {
 [Destination Currency].[Destination Currency].[Euro],
 [Destination Currency].[Destination Currency].[US Dollar]
} on rows
from [Adventure Works]
where ([Date].[Fiscal].[Month].[June 2004]);

The output of this MDX statement provides you with the June 2004 Internet Sales Amount value in the US dollar and Euro currencies, as shown in the following table:

Currency	Internet Sales Amount	Average Rate
Euro	1,969,575.15	.99
US Dollar	$1,949,361.11	1.00

This conversion looks relatively simple but recall that the preceding query is for the month of June 2004 and exchange rates are calculated on a daily basis (and for that matter, intra-day rate). You actually calculate this by taking each day, applying the US Dollar Internet Sales Amount, dividing it by the average rate (which is that day's average exchange rate to the US dollar), and then summarizing the value:

with set [My Currencies] as '{ [Destination Currency].[Destination Currency].[Euro], [Destination Currency].[Destination Currency].[US Dollar] }' select { Crossjoin( [My Currencies], {[Measures].[Internet Sales Amount], [Measures].[Average Rate]} ) } on columns, non empty ...