MDX – 12 Months to Date

Analysis Services 2005 comes with the useful Add Business Intelligence wizard, which will assist you with numerous calculations, including 12 Months to Date. However, I tend to write such calculations myself, especially having found out that the 12 Months to Date MDX generated by the Business Intelligence wizard didn’t satisfy a client’s requirements. Although the calculation works fine at the Month level in a Date dimension, it doesn’t quite work at the day level – and it certainly doesn’t take leap years into account.

The client’s requirement for day level 12 Months to Date was to return an accumulated view of the last 365 days back in the calendar hierarchy. However, for leap years, the calculation should take into account the 29th of February if the current year is a leap year.

In order to get this to work, the first step is to modify the Date dimension so that it contains an attribute to signify whether the current day is affected by a leap year or not. In order to do this:

1. Add a bit column to your date dimension table called AffectedByLeapYear (or similar)

2. Run a SQL Update statement to populate your new column:

UPDATE      dbo.DimTime
SET         AffectedByLeapYear =
            CASE
                WHEN (CalendarYear % 4 = 0)  AND (CalendarYear % 100 != 0 OR CalendarYear % 400 = 0)
                AND DayNumberOfYear >= 60 THEN
                    1
                ELSE
                    0
            END 

 

3. Refresh your data source view

4. Add a new attribute called ‘Affected By Leap Year’ to your date dimension:

 

Now we’re ready to write the MDX for 12 Months to Date. In the MDX Script, the first step is to scope on a member called [Time Analysis].[Time Analysis].&[2], which is the 12 Months to Date member in my Time Utility dimension:

Scope
(
    //Scope on 12 months to date
    [Time Analysis].[Time Analysis].&[2]

Then we need a nested scope on the True member of the ‘Affected By Leap Year’ attribute. This is really important because it means we can isolate the section of the cube that is affected by the leap year. Also, by using Scope(),  we can avoid complex IIf statements – everything is managed cleanly in the Scope() statement:

Scope
(
    //This statement is key - we scope on the cells that we know are affected by the leap year
    //This avoids a big and inefficient iif statement
    [Date].[Calendar].[Date].Members,
    [Date].[Affected By Leap Year].&[True]
) ;

Now we’re ready to perform the actual calculation. Seeing as this is for dates that are affected by a leap year, we need to aggregate a set with an extra member, meaning for any dates in a leap year beyond the 28th Feb, we will aggregate 366 days worth of data. Therefore, the following MDX statement is not too dissimilar to the kind of MDX that gets generated by the Business Intelligence wizard, aside from the fact its operating at the day level:

This = Aggregate
       (
            //We need to go back an extra day here
            {[Time Analysis].[Time Analysis].&[1]} *
            ParallelPeriod
            (
                [Date].[Calendar].[Date], 365, [Date].[Calendar].CurrentMember
            )   : [Date].[Calendar].CurrentMember
        ) ;

We then have a similar statement for the normal dates, which are any dates prior to the 29th Feb in a leap year, or any dates not in a leap year. The difference here is that the set that get aggregated just includes 365 days worth of data. When we put all these pieces together, we get:

Scope
(
    //Scope on 12 months to date
    [Time Analysis].[Time Analysis].&[2]

But we’re not finished yet! We now need deal with the other levels in the Calendar hierarchy, namely Half Years, Quarters, Months and Years. These are all dealt with by a relatively simple statement that sets the current member of the respective hierarchy to its last child. So for a month, we pick up the 12 Months Year to Date figure for the end of the month, for the Quarter we pick up the 12 Months Year to Date for the last month in the Quarter etc etc. For example:

Scope
(
    [Date].[Calendar].[Month].Members
) ;
//For months, always display data from the last day in the month
This = [Date].[Calendar].CurrentMember.LastChild;
End Scope;

Now you have a 12 Months to Date calculation that works at all levels in the Calendar hierarchy. Perhaps some of this is overkill for your requirements? I have spoken with users in the past who have only requested 12 Months to Date at the Month level – which is obviously a lot simpler. If you’re doing a 12 Months to Date calculation, then it almost goes without saying that you have to make it work at the Month level! But as for the other levels, such as Quarter, you may be ok just leaving these as NULL. Certainly the Time Intelligence wizard leaves Years, Half Years and Quarters out, just putting ‘N/A’. But all comes down to user requirements – and as we all know – you have to keep the users happy 🙂