Problem with ParallelPeriod() function
One of the clients had very weird Date dimension. They simply mapped weeks to months with no pattern in mind. For example, [May 2008] in that dimension has 5 weeks, whereas [May 2009] – 4. Still, they want to use ParallelPeriod() to compare same members between different years and expect to see meaningful data. Looks like ParallelPeriod doesn’t work that way. Whenever there is a user-defined hierarchy and same child in different years belongs to the different parents (Months), ParallelPeriod will make a shift following the hierarchy path and return a child under the same parent of the parallel period. For example, if you have [Week 22, 2009] member and want to select parallel member from year 2008, the [Week 23 2008] will be returned and the reason: [Week 22, 2009] member is the first child of [June 2009], so ParallelPeriod will follow Year-Quarter-Month-Week hierarchy and select the first child of [June 2008] which is [Week 23 2008].
When you run
member [ParallelP] as
(ParallelPeriod([Accounting Period].[Year], 1, [Accounting Period]), [Net Wt]).Item(0).Name
select { [ParallelP]} on 0,
{[Dim Accounting Period].[Accounting Period].[Month].&[May 2009].Children
,[Dim Accounting Period].[Accounting Period].[Month].&[June 2009].Children}
on 1
you get
See the shift. Not good
To be able to avoid this, Week level has to be ranked by the natural order, and sibling at the same position of the previous year has to be referenced:
with
member [Net Wt LY] as
iif([Dim Accounting Period].CurrentMember.Level is [Dim Accounting Period].[Week],
–at the week level use ranking instead of ParallelPeriod()
(descendants(
ancestor([Dim Accounting Period].CurrentMember,
[Accounting Period].[Year]
) as alias.PrevMember
,[Dim Accounting Period].[Week]
).Item(
rank([Dim Accounting Period].CurrentMember,
descendants(alias, [Dim Accounting Period].[Week])
) – 1
), [Net Wt]
)
–at any other level use ParallelPeriod safely
,(ParallelPeriod([Accounting Period].[Year], 1, [Accounting Period]), [Net Wt])
)
select {[Net Wt], [Net Wt LY]} on 0,
{
[Dim Accounting Period].[Accounting Period].[Month].&[May 2008].Children
,[Dim Accounting Period].[Accounting Period].[Month].&[June 2008].Children
,[Dim Accounting Period].[Accounting Period].[Month].&[May 2009].Children
,[Dim Accounting Period].[Accounting Period].[Month].&[June 2009].Children
,[Dim Accounting Period].[Accounting Period].[Month].&[October 2008]
}
on 1
from [Sales Invoice]
You can see, that data for the same members (Weeks) under different years is now matching.
Konstantin Gorakine's BI Lingual Blog 
Leave a comment