In the financial services world, a common requirement is to annualized a value. We've made several attempts at this. The challenge comes when using a standard Date hierarchy (Year, Quarter, Month, Date). We need to annualized a measure based on a running total of the last 6 months. We also need to take into account the early dates of the warehouse where X Months might not be available by multiplying the aggregate it by Y divided by the count of non-empty members.
This seems to work for this part of the problem:
Create Member CurrentCube.[Measures].[Rolling 6m Contribution]
AS Case
When COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1 < 6
Then Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
* 6 /
(COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1)
Else
Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
End,
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;
We've also tried this with the YTD function, but using YTD give a small sample size for the annual figure in the first months of each year.
Next, we need to be able to make the rolling sum work at all levels of the date hierarchy. This basically needs to act like LastNonEmpty aggregation on normal measures. We created a second measure:
Create Member CurrentCube.[Measures].[Rolling 6a Contribution]
AS
([Measures].[Rolling 6m Contribution],
ClosingPeriod([Date].[Calendar Year Hierarchy].[Month],
[Date].[Calendar Year Hierarchy].CurrentMember)),
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;
This seems to work for Year and Quarter except that the final Year has a Null value.
Does anyone have any suggestions on annualization in general or what we're doing wrong in the code above?
Im am not sure that I can solve all your business/MDX problems but it seems to me that you will need a sliding time member.
Here is a thread that I have participated in that perhaps can give you a clue: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996037&SiteID=1
Mosha have also a good post on his BLOG(http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx)
HTH
Thomas Ivarsson
|||Thanks for the suggestion Thomas. Both posts as well as Danial Shroyer white paper have great info.
However, my problem here isn't so much getting the running total as making it work with a user hierarchy. In the first code sample above, I can get the the sliding total. I can't figure out how to have the year and quarter levels reflect the monthly amount like a LastNonEmpty measure. For example, in November or 2006, the 2006 level needs to be November's sliding total (times 2 to annualize). The second code sample almost works.
|||To clarify a bit, what I need is a running total that can be shown along side other measures and calculated measures in a report. The follwing MDX:
Create Member CurrentCube.[Measures].[Rolling 6m Contribution]
AS Case
When COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1 < 6
Then Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
* 6 /
(COUNT(LASTPERIODS(6, [Date].[Month].CurrentMember), EXCLUDEEMPTY) + 1)
Else
Aggregate(LASTPERIODS ( 6, [Date].[Month].CurrentMember),[Measures].[Total Contribution])
End,
Format_String = "Currency",
Non_Empty_Behavior = [Measures].[Total Contribution],
Visible = 1;
Yields the following results in OWC:
The adustment for the first 5 months of data in the warehouse works and the running sum works for 2005, all quarters, and all months. The remaining problem is the missing total for 2006. Can anyone tell my why this doesn't work?
|||For anyone who's interested, the problem in the above example is that 2006 hasn't "closed" yet. ClosingPeriod returns the last child of Year, but since there's no fact data in the cube for December yet, the result is null.
It would be great if you could set aggregation on a calculated member (LastNonEmpty).
