PY YTD calculated measure that can handle leap years MDX

Question

This might be a longshot, but maybe there is an ssas/mdx guru here that has stumpled upon the same problem regarding aggregated calculations and leap years. So what I am trying to do, is to create a PY YTD calculated measure in an ssas cube that can handle leap years. The issue I am facing is that the calculated measure becomes extremely slow based on the logic below (see code sample). Has anyone found a better way to handle leap years or has a document with best practise to share ? I am assume that if-statements and NonEmpty function could be performance deadly for a calculated measure. All tips are greatly appreciated (Doesn't have to be a solution).

-- YTD PRIOR YEAR
[Time Calculations].[YTD Pr Yr] = IIF([Calendar].[Year - Month - Day].CurrentMember IS [Calendar].[Year - Month - Day].[Month].&[201702]
,Aggregate(
    NonEmptyCrossjoin({[Time Calculations].[Current Period]},
    PeriodsToDate(
        [Calendar].[Year - Month - Day].[Year],
[Calendar].[Year - Month - Day].[Day].&[2016-02-29 00:00:00.000])
))
,IIF([Calendar].[Year - Month - Day].CurrentMember IS [Calendar].[Year - Month - Day].[Month].&[201602]
,Aggregate(
    NonEmptyCrossjoin({[Time Calculations].[Current Period]},
    PeriodsToDate(
        [Calendar].[Year - Month - Day].[Year],
[Calendar].[Year - Month - Day].[Day].&[2015-02-28 00:00:00.000])
))
,(Aggregate(
    NonEmptyCrossjoin({[Time Calculations].[Current Period]},
    PeriodsToDate(
        [Calendar].[Year - Month - Day].[Year],
        ParallelPeriod(
           [Calendar].[Year - Month - Day].[Year],1,

TAIL(DESCENDANTS([Calendar].[Year - Month - Day].CurrentMember
, [Calendar].[Year - Month - Day].[Day]),1).ITEM(0)))

    )
    )
    )
    )
);

Best regards, Rubrix

When you say "problem", what does that mean exactly? How are you expecting it to behave? I could give you a solution that would solve the problem efficiently but it might not meet your particular business needs. — Mitch Schroeter
Hey @MitchSchroeter thank you for your reply. The problem with leap years and Previous Year year-to-date is that "Parallel period" is not calender aware, so it does not know that in February 2019 there is 28 days where as 2020 February has 29 days (i.e Leap year). So when you compare 2020-02-29 with previous year it will return null since there is no member 29 for 2019 February. The solution that I posted work, but it is rather slow. So I was wondering if someone had used a different approach. — Rubrix
So what I want it to do, is to always compare February with 28, so when ever there is a 29th member it should be changed to 28. Then you will never encounter situations like 2020-02-29 vs 2019-02-29 which returns NULL since we all know that there is no 2019-02-29 for the year 2019. — Rubrix

Mitch Schroeter Mitch Schroeter · Accepted Answer · 2020-04-15T16:51:53

Try the following, some caveats follow:

CREATE MEMBER CURRENTCUBE.[Time Calculations].[YTD Prior Year] AS NULL; 

/* Make sure the scope is for all days in all years in your calendar year */
Scope([Invoice Date].[Calendar Year].[Calendar Year].members, [Invoice Date].[Calendar Day].members); 

    // YTD PRIOR YEAR
    ([Time Calculations].[YTD Prior Year] =
        iif(
            /* Check to see if the prior year member is empty */
            isempty(
                ParallelPeriod(
                    [Invoice Date].[CY Hierarchy].[Calendar Year],
                    1,
                    [Invoice Date].[CY Hierarchy].CurrentMember
                ).MemberValue
            ),
            /* If so, use the .LastChild */
            Aggregate(
                Crossjoin(
                    {[Time Calculations].[Current Period]},
                    PeriodsToDate(
                        [Invoice Date].[CY Hierarchy].[Calendar Year],
                        ParallelPeriod(
                            [Invoice Date].[CY Hierarchy].[Calendar Year],
                            1,
                            Ancestor(
                                [Invoice Date].[CY Hierarchy].CurrentMember,
                                [Invoice Date].[CY Hierarchy].[Calendar Month]
                            )
                        ).LastChild
                    )
                )
            ),
            /* Otherwise just use the prior year */
            Aggregate(
                Crossjoin(
                    {[Time Calculations].[Current Period]},
                    PeriodsToDate(
                        [Invoice Date].[CY Hierarchy].[Calendar Year],
                        ParallelPeriod(
                            [Invoice Date].[CY Hierarchy].[Calendar Year],
                            1,
                            [Invoice Date].[CY Hierarchy].CurrentMember
                        )
                    )
                )
            )
        )
    ); 

End Scope;

One possible hitch is that if you have several days in a row where there are no transactions, .LastChild might not work accurately. I didn't have that situation when I originally developed this code. It was specifically developed to deal with this exact case of prior year-to-date and leap year nuances. It might need to be tweaked if that's the case.

This assumes that you have a proper time dimension and a time calculations dimension, which it looks like you do from the code sample you provided.

I would say that the performance of this solution is pretty great even on large cubes (hundreds of millions of rows).

PY YTD calculated measure that can handle leap years MDX

1 Answers