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

• Day (Dimension Key) - post_date

• Month - post_year, post_month

• Quarter - post_year, post_quarter = DatePart(quarter, "post_date"))

• 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,

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

StackExchange Database Administrators Q#14266, answer score: 2