Post reply

Many-To-Many currency conversion - discrepancies

  • September 25, 2014 at 7:11 am

    #150783

    Hi,

    I created many-to-many currency conversion project based on Wizard (i'm not an expert).

    This solution has Pivot currency (all amounts are converted to Pivot currency first and then to Reporting Currencies).

    My issue is that I see discrepancies between 'standard' currency conversion(calculated by hand) and by using Pivot currency.

    Example:

    Pivot currency is USD

    Amount: 104120.00 (GBP)

    Exchange Rate GBP -> USD: 1.63783 (http://www.oanda.com)

    Exchange Rate USD -> GBP: 0.61050 (http://www.oanda.com)

    Standard conversion (like you usually convert currency): 104120.00 (GBP) * 1.63783 = 170530,86 (USD)

    SSAS approach (Pivot currency): 104120.00 (GBP) / 0.61050 = 170548,73 (USD)

    The discrepancy is around 18 dollars.

    Do you have any idea how to resolve this issue? Change MDX calculations?

    Here is my current MDX code (generated by wizard):

    Scope ( { Measures.[Amount]);

    Scope( Leaves([Exchange Transaction Date]) ,

    [Reporting Currency].[USD],

    Leaves([Exchange Transaction Currency]));

    This = [Reporting Currency].[Local] / Measures.[Report Rate];

    End Scope;

    Scope( Leaves([Exchange Transaction Date]) ,

    Except([Reporting Currency].[Currency Id].[Currency Id].Members,

    {[Reporting Currency].[Currency Id].[Currency Id].[USD],

    [Reporting Currency].[Currency Id].[Currency Id].[Local]}));

    This = [Reporting Currency].[Currency Id].[USD] * (Measures.[Report Rate],

    LinkMember([Reporting Currency].[Currency Id].CurrentMember,

    [Exchange Transaction Currency].[Currency Id])) ;http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

    End Scope;

    End Scope;

    Thanks 🙂

  • September 25, 2014 at 8:01 am

    #1748171

    The issue looks to be that your two rates aren't equivalent, i.e. this formula

    (Us-->GB Rate) = 1/(GB-->US rate)

    turns out to not be true with the values you're providing. Unless there are different service charges depending on which direction you're converting from, that's a bit of a Puzzler (i.e. would be the type of thing I'd question with the provider of exchange rates).

    Either way though, it does sound like you'd have to decide what fits with the business requirement you're being asked to meet, and then make sure that's the rate being used. If that entails having to force SSAS to use the other calc, then so be it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply