Hi yayomayn,

thanks again for your useful tips.

I just want to extend this discussion.

I wrote some MDX code like this in order to get the value in a reference currency across the time hierarchy:

SCOPE

(MeasureGroupMeasures("Exchange Rates"), Leaves([Time]), [Currencies].[ISO Code].[ISO Code]);

[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);

END SCOPE;

SCOPE

(MeasureGroupMeasures("Exchange Rates"), [Time].[Month].[Month], [Currencies].[ISO Code].[ISO Code]);

[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);

END SCOPE;

-- Scope for the Quarters

SCOPE

(MeasureGroupMeasures("Exchange Rates"), [Time].[Quarter].[Quarter], [Currencies].[ISO Code].[ISO Code]);

[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);

END SCOPE;

-- Scope for the Years

SCOPE

(MeasureGroupMeasures("Exchange Rates"), [Time].[Year].[Year], [Currencies].[ISO Code].[ISO Code]);

[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);

END SCOPE;

//-- Scope for the All members

SCOPE

(MeasureGroupMeasures("Exchange Rates"), [Time].[Year - Week - Date], [Currencies].[ISO Code].[ISO Code]);

[Measures].[OOH Bookings Gross Sales EUR] = [Measures].[OOH Bookings Gross Value]* ValidMeasure([Measures].[Exchange Rate To Euro]);

END SCOPE;

The idea is when roll up get the last non empty value of the period, i.e., for a month I get the value with the exchange rate of the last day of the month and so on.

My code works, however I am not conformed because:

- When I reapeat and repear the same code just changing some paremeter I feel like something is not smart enough.

- I am wondering if the order of the scope statements matters or not.

- At the beggining I was getting wrong results for the year level but after comment and uncomment some code it works :w00t:

The challenge hier is not to get the conversion with the rate at the transaction rate but dinamically assign a fx rate depending on the selected date. I am building snapshots of the opend orders and I want to be able to show the value of them in a selected date.

Any comment would be appreciated.

Kind Regards,