If we have the following query in the [Adventure Works] cube of the BD AdventureWorksDW2014 :
WITH MEMBER [Measures].[Previo] AS
( [Date].[Calendar].currentmember.PrevMember,[Measures].[Customer Count])
MEMBER [Measures].[Paralelo] AS
(ParallelPeriod([Date].[Calendar].[Calendar Year], 1,
[Date].[Calendar].CurrentMember), [Measures].[Customer Count])
SELECT NON EMPTY { [Measures].[Previo], [Measures].[Paralelo],
[Measures].[Customer Count] } ON COLUMNS,
NON EMPTY { ([Date].[Calendar].[Calendar Year].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( { [Date].[Calendar].[Month].&[2010]&[12], [Date].
[Calendar].[Month].&[2011]&[12], [Date].[Calendar].[Month].&[2012]&[12],
[Date].[Calendar].[Month].&[2012]&[11], [Date].[Calendar].[Month].&
[2011]&[11], [Date].[Calendar].[Month].&[2010]&[11],
[Date].[Calendar].[Month].&[2010]&[10], [Date].[Calendar].[Month].&
[2011]&[10], [Date].[Calendar].[Month].&[2012]&[10] }) ON COLUMNS
FROM [Adventure Works])
Produces the following result:
Previo Paralelo Cuenta de Clientes CY 2010 (null) (null) 14 CY 2011 14 14 651 CY 2012 2216 2216 988
It is observed that per year, the Natural Measure of the months that have been filtered is added.
But the same does not happen with the Calculated Measures, which are added for the whole year without taking into account the filtered months. How do we manage to filter the calculated measures (Previous and Parallel)?
On the other hand, when another next level of the hierarchy is involved, such as the month in the following query:
WITH MEMBER [Measures].[Previo] AS
([Date].[Calendar].currentmember.PrevMember,[Measures].[Customer Count])
MEMBER [Measures].[Paralelo] AS (ParallelPeriod([Date].[Calendar].
[Calendar Year], 1, [Date].[Calendar].CurrentMember), [Measures].
[Customer Count])
SELECT NON EMPTY { [Measures].[Paralelo], [Measures].[Previo],
[Measures].[Customer Count] } ON COLUMNS,
NON EMPTY { ([Date].[Calendar].[Month].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( { [Date].[Calendar].[Month].&[2010]&[12], [Date].
[Calendar].[Month].&[2011]&[12], [Date].[Calendar].[Month].&[2012]&[12],
[Date].[Calendar].[Month].&[2012]&[11], [Date].[Calendar].[Month].&
[2011]&[11], [Date].[Calendar].[Month].&[2010]&[11],
[Date].[Calendar].[Month].&[2010]&[10], [Date].[Calendar].[Month].&
[2011]&[10], [Date].[Calendar].[Month].&[2012]&[10]}) ON COLUMNS
FROM [Adventure Works] )
Produces the following result:
[Paralelo] [Previo] [Cuenta de Clientes] December 2010 (null) (null) 14 October 2011 (null) 185 221 November 2011 (null) 221 208 December 2011 14 208 222 October 2012 221 269 313 November 2012 208 313 324 December 2012 222 324 354
It is observed that the calculated SI measurements are calculated correctly involving the months filters.