October 6, 2006 at 8:09 am
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.
October 6, 2006 at 10:35 am
With a little more detail of your current cube structure and the current MDX we could possibly add more, but as a first port of call, I would suggest looking at the PeriodsToDate function.
Steve.
October 9, 2006 at 8:19 am
Ok, I'm looking at the PeriodsToDate function now... I'll keep you posted if this worked.
Some more details, i have 2 fact tables.
The first fact table (AUMFact)has the [Assets under Management] measure, this is a monthly measure, but I cannot take the totals from each month and sum them. It represents what the client owns at the time the data is captured.
The Second fact table (RepCommissionFact) has the [Rep Commission] measure captured monthly; this measure can be summed with the previous months to show what the Rep has made in commission revenue.
Traditionally, I would have added a new measure to RepCommissionFact and each month I would have calculated the last 12 months and inserted the value into the table.
I need to be able to calculate an ROA… Return on Assets. The calculation is the last 12 months of [Rep Commission] divided by [Assets under Management]
Hope this makes it clearer.
Thanks
October 9, 2006 at 9:02 am
So something like below (pseudo-mdx) as a calc'd measure might work?
if ( [TimeDim].CurrentMember.Level = TimeDim.Levels.Month,
Sum(
PeriodsToDate([TimeDim].[Month], [TimeDim].CURRENTMEMBER)
, [Measures].[Commision]) / [measures].[AUM]
)
Steve.
October 18, 2006 at 9:28 am
That works! thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply