In my prior blog post, I provided some details regarding currency conversion via measure expressions in SSAS.    I recently attempted to implement currency conversion for some inventory measures and discovered a limitation with measure expressions – they do not work for measures with a ‘Last non-empty value’ aggregation.  As an alternative to measure expressions, the currency conversion may be implemented in the cube’s MDX script. 

The basic expression for currency conversion is:

[Measure in Target Currency] = [Measure in Source Currency] * [Currency Conversion Rate]

In this example, we have a measure that represents a value converted from the source currency to a target currency.  The initial value of this measure is set to the source currency (specified by the Source column). 

Now, we need to overwrite the measure’s value in the MDX script so it represents the measure in the target currency (in this example, the measure we’re overwriting is called ‘Quantity Available Cost Converted’. The measure ‘Quantity Available Cost Local’ is in the original/source currency).  The target currency is selected by the end-user.

SCOPE([Measures].[Quantity Available Cost Converted]   
  ,LEAVES([Source Currency])   
  ,LEAVES([Destination Currency])   
  ,LEAVES([Currency Rate Type])   
  ,LEAVES([Dates]));   
THIS = [Measures].[Quantity Available Cost Local] * [Measures].[Currency Rate Ov];   
END SCOPE;

The SCOPE function ensures that we are only affecting the portion of the cube specified in the function.  In this case, the function overwrites the measure Quantity Available Cost Converted where it intersects the leaves of the Source Currency, Destination (target) Currency, Currency Rate Type and Dates dimensions.  The LEAVES function is important since the measure uses the ‘Last Non-Empty Value’ aggregation and is therefore semi-additive.  We only want to perform the calculation at the leaf level and not use the aggregations.