ParallelPeriod returning null for Feb. 29 in date dimension

ssas

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

Best Answer

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