Hello,

I have a cube with monthly data. For simplicity, let’s imagine 2 facts.
1. Monthly Assets under Management
2. Revenue

I need a calculation that will take the sum of the current month + last 11
months of Revenue divided by the current months Assets under Management.

Right now, I have it working for a month, but I can’t seem to grasp how to
create a calculation to get the previous 11 months plus current months sum of
Revenue.

[Measures].[Rep Commission]
/
[Measures].[Assets under Management]

Any help or direction would be greatly appreciated.

You don't mention which version of AS you are using or how your time
dimension is structured, but it would look something like the following
in AS2005...


SUM([Time].[Calendar].CurrentMember.Lag(11):[Time].
[Calendar].CurrentMember,[Measures].[Rep Commission])
/
[Measures].[Assets under Management]


The only other thing you need to think about is what you want the users
to see when the current time member is not at the month level. You might
choose to have some variation of the calculation or simply even display
"N/A" or a null.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <2E48ADEB-6C39-45B6-BF07-047EC012804F (AT) microsoft (DOT) com>,
appdevtech (AT) online (DOT) nospam says...