Home Forums Data Warehousing Analysis Services Convert a sales value to the reference currency with the exchange rate of a specific date RE: Convert a sales value to the reference currency with the exchange rate of a specific date

  • Was not so difficult as I thought, however, it took me several hours to understand how could it work:

    with

    MEMBER [Measures].[Current Rate To Euro] AS (

    ([ISO Currencies].[ISO Curr Code].CurrentMember,[Time].[Date Id].CurrentMember, [Measures].[Exchange Rate To Euro]))

    MEMBER [Measures].[Net Sales Euro] AS (

    SUM([ISO Currencies].[ISO Curr Code].[ISO Curr Code],([Measures].[OOH Net Sales Dom Snapshot]/[Measures].[Current Rate To Euro]))

    )

    select {[Measures].[Net Sales Euro], [Measures].[OOH Net Sales Dom Snapshot],[Measures].[Current Rate To Euro]} on 0,

    non empty {[Time].[Date Id].&[2580]} -- specific datatime, in my db is the 23 Jan. 2014

    *{[Customers].[PDU Description].[PDU Description]} on 1 -- An attribute of the customer hier.

    from [DM ISR DEV]

    ;

    I think all of my confusions come from my Sql background. A calculated member is a member that is resolved by calculating an MDX expression to return a value, not a set, for this reason the key to solve my problem was the SUM function.

    Paul Hernández