Does leap year has impact on the parallel periods calculation ?

Dineshkumar Nagarajan

On a Daily basis for MTD calc, Apr 1st 2023 value is referred to Mar 31st 2024 for the following MDX. 


sum(PeriodsToDate([Date Dimension].[Calendar Year Hierarchy].[Calendar Month], ParallelPeriod([Date Dimension].[Calendar Year Hierarchy].[Calendar Year], 1, [Date Dimension].[Calendar Year Hierarchy].CurrentMember)), <Measures>)

0

Comments

4 comments

  • Comment author
    Daren Drummond

    Hi DineshKumar, these functions work based on the rows that are actually in your Date Dimension table and not the Gegorian Calendar (like DAX).  If you have a special leap-year row in your date dimension, then this will certainly influence the results. 

    The best way to investigate your issue is to run a query that returns separate columns for various components of your expression.  Select 1 column for the  [Date Dimension].[Calendar Year Hierarchy] current Member Measure value, 1 column for a ParallelPeriod Measure value, then 1 column for the combined PeriodsToDate( ParallelPeriod()) Mesure value.

    If you can post such a query along with the results, then I can assist further.

    0
  • Comment author
    Dineshkumar Nagarajan

    Thanks Daren. Here's the data points. 

    This is the calendar we have, regular Gregorian. 

    All the values after Feb 29th shifted one day up. 

    Red Mark - Comparing - 2022 Mar 31 vs 2023 Mar 31
    Blue Mark - Comparing - 2023 Mar 31 vs 2024 Mar 30

    MDX used: 

    MTD - Sum(PeriodsToDate([Date Dimension].[Calendar Year Hierarchy].[Month], [Date Dimension].[Calendar Year Hierarchy].CurrentMember), <Measure>)

    PYMTD - sum(PeriodsToDate([Date Dimension].[Calendar Year Hierarchy].[Month], ParallelPeriod([Date Dimension].[Calendar Year Hierarchy].[Year], 1, [Date Dimension].[Calendar Year Hierarchy].CurrentMember)), <Measure>)

    I'd expect to get current day to be compared same day of last year.

    0
  • Comment author
    Daren Drummond

    Hi DineshKumar,  we confirmed that this is a bug.  We are working on a fix and expect it to be fixed in a near-term release.

    0
  • Comment author
    Dineshkumar Nagarajan

    Closing the loop - issue got resolved in the Version 2024.2.1.7014

    0

Please sign in to leave a comment.