Does leap year has impact on the parallel periods calculation ?
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>)
Comments
4 comments
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.
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.
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.
Closing the loop - issue got resolved in the Version 2024.2.1.7014
Please sign in to leave a comment.