YTD Calculation Alternative

Question

The following are the data set that I have.

As of Date  ITD Written Premium  MTD Written Premium
12-31-2008                         200                               50
01-31-2009                         300                             100
02-28-2009                         380                               80

In my time dimension, I have a hierachy set up as the folloing.
As of Dates
 - As of Year
 -- As of Date

To calculate for the YTD Written Premium, I have created a calculated member with the expression below.
SUM(YTD([Date].[As of Dates]),[Measures].[MTD Written Premium])

The calculation expression works fine by summing all the MTD Written Premium for the year.  However, it relies on the MTD Written Premium being correct for every month.  Now, what if the MTD data is not reliable due to whatever data issues, but the ITD data is very reliable.

I want to see if there is a way in MDX that I can take the current month ITD Written Premium minus the prior year end ITD Written Premium to calculate the YTD Written Premium.  In my data set example, the YTD written premium is 180.  My current expression is to add the MTD written premium for the year, 100 + 80 = 180.  I want to find the expression that does the calculation using the ITD written premium instead, 380 - 200 = 180.  Any help on this expression is greatly appreciated.

Mitch

Answer 1

Hi Mitch you could do something like the following:

([Date].[As of Dates].CurrentMember, [Measures].[ITD Written Premium])  // Current ITD
-
([Date].[As of Dates].CurrentMember.Level.Members.Item(0), [Measures].[ITD Written Premium]) // Opening balance ITD

Regards
Stefan

Answer 2

Stefan,

Thanks for the suggestion.  Unfortunately, the expression for the Opening balance ITD doesn't equal the ITD written premium of the prior year end.  It pulls the ITD written premium from the very first as of date.  The expression works with my example data set.  However, if there are multiple year ends in the data set, the suggested expression doesn't work.  So, the key is how can I pull the IT written premium of the prior year end.

thx,
Mitch

Answer 3

Hi Mitch

([Date].[As of Dates].CurrentMember, [Measures].[ITD Written Premium])
-
(
Ancestor
(
[Date].[As of Dates].CurrentMember,
[Date].[As of Dates].[As of Year]
).item(0).item(0).PrevMember.LastChild
,
[Measures].[ITD Written Premium]
)

Answer 4

Stefan,

Thanks so much for the help.  On a separate subject, I have been trying to build my skills on MDX.  I have got a couple of books, but not very easy to follow.  I have looked for training, but found class offered by Chris Webb in UK (Not likely my company will fly me there).  Can you provide some pointers on how to learn MDX to jump from a beginner (like me with the basics) to a mid-level or expert-level person (like yourself)?  I just thought it would be easier for someone who is already there to show me the path.  I have googled way too long on this training subject.

Many Thanks,
Mitch

Answer 5

Hi Mitch

actually I have two reference books:

- MDX Solutions With SQL Server Analysis Services 2005 And Hyperion Essbase 2nd Edition (the best)
- Microsoft SQL Server 2008 MDX

I followed the MDX course on SQL Server 2000 (but it was not really helpful)

Regards
Stefan