Tag Archives: Analysis Services

MDX: Tail

To retrieve the last member of a set you can use the TAIL() function.

The following example returns the total turnover over the last member in 2020-02 for company 4711.

SELECT {
	TAIL(NONEMPTY([DatumStapel].[JMT].[T].Members, { ([Datenherkunft].[Datenherkunft].&[200], [Company].[Company].&[4711], [DatumStapel].[JMT].[M].&[2020]&[2]) }))
} on columns
, { [Measures].[Umsatz] } on rows
FROM [Finance]

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