SSAS sum measure based on daily currency exchange rates

Question

This measure, successfully converts my measure to the currency selected. So if I get data, from the 1st of the month, till the 15th of the month, what will happen is that the exchange rate taken for the currency selected would be of the 15th of the month.

 CREATE MEMBER CURRENTCUBE.[Measures].[Comp Money In] AS null;    
SCOPE([Dim Time].[Date Key].members);     
[Measures].[Comp Money In]=      [Measures].[_Comp Money In]/[Measures].[Last Currency Rate];  

end scope;    

However what I want is that the aggregated amount, is taking into consideration the DAILY closing of the exchange rates. So the 1st of the month aggregated with the 1st of the month exchange rate, 2nd the same, 3rd... So on so fourth.

I am sure that in my fact having the currency exchange rates, there is 1 exchange rate per day which would be the closing one.

How can I do this? I am on MS SQL 2008 R2 standard, so I cannot use measure expressions, or semi additive measures.

Answer 1

I see this is an old question, but in case you are still looking... Can you create an intermediate measure in your fact table to contain the converted value per day and then sum it up as and when needed?

OR Alternatively, create a temp. measure in your mdx, then enumerate this for the date range required and then do an aggregate?

Wrote this based on AdventureWorks and this link https://social.technet.microsoft.com/Forums/sqlserver/en-US/e4cd7d8e-a829-45dc-afe1-b98964d2dbc9/weighted-average-calculation-using-mdx

with 
member [Measures].[Daily Sales Amount] as
    sum
    (
        [Date].[Calendar].currentmember 
        , [Measures].[Internet Sales Amount]
    )
member [Measures].[Cumulative Sales] as
sum
(
    [Date].[Calendar].[Month].&[2006]&[1].children
    *
    [Destination Currency].[Destination Currency Code].[Destination Currency Code].allmembers
    , [Measures].[Daily Sales Amount] * [Measures].[End of Day Rate]
)
/ [Measures].[End of Day Rate]
select 
{
    [Measures].[Cumulative Sales]
}
on 0,
non empty 
{
    [Date].[Calendar].[Month].&[2006]&[1]
}
on 1
from [Adventure Works]