I have a calendar date dimension backed by a physical table of dates (originally created on SQL Server 2000, hence the datetime instead of date):
CREATE TABLE [dbo].[PostDate_Dimension]( [post_date] [datetime] NOT NULL PRIMARY KEY, [day_of_year] [int] NOT NULL, [day_of_month] [int] NOT NULL, [month_of_year] [int] NOT NULL, [post_year] AS (datepart(year,[post_date])), [post_month] AS (datepart(month,[post_date])), [post_day] AS (datepart(day,[post_date])) )
The Post Date dimension has four attributes (with member key columns listed, some of which are calculated in the DSV):
- Day (Dimension Key) - post_date
- Month - post_year, post_month
- Quarter - post_year, post_quarter =
- Year - post_year
It's nothing too fancy, obviously. I also have a few calculated measures that use ParallelPeriod to calculate YTD figures from the previous year, for quick side-by-side comparison without requiring the user to choose a specific slice of dates. Just pick the current year, and it will find the latest date with sales in it, then compare to that same range from the previous year.
Finding the appropriate date in the previous year normally boils down to this:
ParallelPeriod( [Post Date].[Post Date].[Year], 1, Tail( NonEmpty( Descendants( [Post Date].CurrentMember, , Leaves ), Measures.[Total Price] ), 1 ).Item(0) )
The Tail call is where it finds the latest date beneath the currently selected Post Date member (typically the current year). That works fine. But if that returns Feb. 29, meaning the last sale for a particular combination of dimension members occurred on Feb. 29, then it passes Feb. 29 into the ParallelPeriod function, which subsequently returns null. And then the previous-year YTD measure also returns null.
So, in a nutshell: Based on this particular schema, is there a simple way to have ParallelPeriod behave nicely for Feb. 29 inputs? If it just returns Feb. 28 of the previous year, that's fine.
A few things I've tried:
- Using this expression to adjust the Post Date member:
Iif(MONTH([Post Date].[Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].[Post Date].CurrentMember.Member_Caption) = 29, [Post Date].[Post Date].CurrentMember.PREVMEMBER, [Post Date].[Post Date].CurrentMember)
This works, but the code would be atrocious, since I'd have to replace all
[Post Date].[Post Date].CurrentMemberwith
Tail(NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]), 1).Item(0)).
- Using Except to remove all Feb. 29 dates from the results of
NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]). I can't figure out the proper syntax (if any) to get a set of all Feb. 29s from the dimension.
- Creating a .NET assembly with a user-defined function that takes a member as a parameter, and returns the previous member if it's a Feb. 29. It seems like the classes in Microsoft.AnalysisServices.AdomdServer are extremely limited and don't even allow for this basic task (nor even retrieving the member key as a date value).