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]
) ;
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]
) ;
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
) ;
End Scope;
Scope
(
//Now we scope on the ‘normal’ unaffected dates
[Date].[Calendar].[Date].Members,
[Date].[Affected By Leap Year].&[False]
) ;
This = Aggregate
(
//We are in a normal year (or in a leap before 29th Feb), just go back the
//standard 365 days
{[Time Analysis].[Time Analysis].&[1]} *
ParallelPeriod
(
[Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember
) : [Date].[Calendar].CurrentMember
) ;
End Scope;
End Scope ;
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 🙂
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar