# ParallelPeriod returning null for Feb. 29 in date dimension

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):

1. Day (Dimension Key) - post_date
2. Month - post_year, post_month
3. Quarter - post_year, post_quarter = DatePart(quarter, "post_date"))
4. 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.

EDIT:

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].CurrentMember with 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).
-
A quick and dirty fix might be to put an exception for feb 29 in a SCOPE statement. Try a little google-fu. Never bumped into this issue before but a google search for 'mdx parallelperiod feb 29 leap year' turns up a bunch of blog postings and suchlike that might be of help – ConcernedOfTunbridgeWells Mar 2 '12 at 14:23

Another option would be to use a union to get parallel periods for the current and previous member. Then extract the first item out of this, which will always be the current member (except in the case of Feb 29 where it should fall back to the previous member).

That is, effectively coalesce with the previous member parallel period as per below:

union
(
PARALLELPERIOD(
[Date].[Fiscal Detail].[Fiscal Year],1,
[Date].[Fiscal Detail].currentmember)
*(will be null if Feb 29)*
,
PARALLELPERIOD(
[Date].[Fiscal Detail].[Fiscal Year],1,
[Date].[Fiscal Detail].currentmember.lag(1))
*(will be the prior year Feb 28 when above is Feb 29 )*
).item(0)  *(get the first member out of the union set)*
-
Ah, much better than that crap I cobbled together. I'll play around with that a bit. – db2 Aug 18 at 11:09

Alright, I think I've got a workaround I can live with. First, I created a named set in the cube, called Feb29:

Filter(
[Post Date].[Post Date].[Day].ALLMEMBERS,
MONTH([Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].CurrentMember.Member_Caption) = 29
)

Then I've amended the portion of the calculation which looks for the latest sale date (note the - Feb29):

ParallelPeriod(
[Post Date].[Post Date].[Year],
1,
Tail(
NonEmpty(
Descendants(
[Post Date].CurrentMember,
,
Leaves
),
Measures.[Total Price]
) - Feb29,
1
).Item(0)
)

Thus when it looks for the latest sale date in, say, 2012, it will ignore Feb. 29, and compare to the previous year's sales from Jan. 1 to Feb. 28 instead. I'm still open to suggestions if there's a better way to do this (particularly that awful Filter expression I used).

-