MDX: ParallelPeriod with CurrentMember

Problem

You want to create a Calculated Member that displays the Totals of the previous year using ParallelPeriod.

Solution

See below for a Year- and at the end of the post for a Month-based version.
It is important to mention, that the Hierarchy used in ParallelPeriod function is the same Hierarchy used on an axis!
And another clue is, to use CurrentMember property on the hierarchy’s level and not below on a Sub-Level (here: J, M or T)!

WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
	([Measures].[Umsatz Negiert], 
	ParallelPeriod([DatumStapel].[JMT].[J]
		, 1
		,[DatumStapel].[JMT].CurrentMember)
	)
'
SELECT 
{ [DatumStapel].[JMT].[J].&[2019] : [DatumStapel].[JMT].[J].&[2020] } ON columns
,{
	[Measures].[Umsatz Negiert], 
	Measures.UmsatzNegiertLY } ON rows
FROM Finance

WITH MEMBER [Measures].[UmsatzNegiertLY] AS
'
	([Measures].[Umsatz Negiert], 
	ParallelPeriod([DatumStapel].[JMT].[M]
		, 12
		,[DatumStapel].[JMT].CurrentMember)
	)
'
SELECT 
{ [DatumStapel].[JMT].[M].&[2019]&[1] : [DatumStapel].[JMT].[M].&[2020]&[12] } ON columns
,{
	[Measures].[Umsatz Negiert], 
	Measures.UmsatzNegiertLY } ON rows
FROM Finance

Leave a Reply

Your email address will not be published. Required fields are marked *