Time Functionality

Something I've worked on a number of times and even spoke about at the ODTUG Kaleidoscope conference is MDX and specifically how to use it to do time related calcs like period-to-date functions and Time Balancing. I've seen quite a few posts on the subject and figured it was worth putting up some examples here.

More recent releases of Essbase will do time balancing natively, but period-to-date is something you still have to work out. My preferred method of doing this is to add an analytic dimension to the database and set the formula in one of the calculated members. For those of you who aren't familiar with the term, an analytic dimension is one that you add to your database that has a single default load member, all other members are calculated. In the case of time functionality I usually call this dimension [View] and have a default member called [Periodic]. I will then have some other members as needed, like [Time Balance], [QTD], [YTD], etc.

For Example:


The period-to-date functionality is pretty easy using the PeriodsToDate MDX function, the key is remembering to wrap it in a SUM function because the PTD function returns a member set, not a value.

YTD:
SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per])

QTD is almost identical with a modification to the generation reference:
SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per])


Time Balancing is little more involved. First you need to flag the members you want time balanced with a User Defined Attribute (UDA). Once you have that set you have to decide what kind of time balancing you wan to do. If you just want to grab the last child member, that's fairly easy.

CASE WHEN IsUDA([Measures].CurrentMember, "TB") THEN IIF(IsLeaf([Time].CurrentMember), [View].[Per],
(ClosingPeriod ([Time].Levels(0), [Time].CurrentMember), [View].[Per]))
ELSE [View].[Per] END

If you want to show the last value with data, known as Time Balance Skip missing, then you would use something like this:

CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE [View].[Per] END

Now you'll want to modify your QTD and YTD functions to work with your Time balancing

QTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(2), [Time].CurrentMember), [View].[Per]) END

YTD
CASE WHEN IsUDA([Measures].CurrentMember, "TB_Last") THEN
IIF(IsLeaf([Time].CurrentMember), [View].[Per],
IIF(NonEmptyCount ([Time].CurrentMember.Children, [View].[Per]) > 0,
([View].[Per], Tail(Filter(Leaves([Time].CurrentMember),
Not IsEmpty ([View].[Per]))).Item(0).Item(0)), MISSING))
ELSE SUM(PeriodsToDate([Time].Generations(1), [Time].CurrentMember), [View].[Per]) END