I was just trying to create two calculated memebers (MAT1, MAT2) under period dimension in my cube and came up with this solution. First, I am trying to explain MAT1 and MAT2.
MAT1 : Last 12 months from the current month. If the current month is Jun’2011, MAT1 will be the months from Jul’2010 to Jun’2011.
MAT2: Last 12 months starting from the same month (given month/current month) of previous year. For the above example, MAT2 will be from Jul’2009 to Jun’2010.
YTD1: Months starting from current year’s January up to the current month. If the current month is Jun’2011. YTD1 is from Jan’ 2011 to Jun’2011.
YTD2: Same months from YTD1 but from the previous year. If current months is Jun’ 2011, YTD2 is from Jan’2010 to Jun’2010.
Below is two snapshots of my period dimension:
The hierarchy is Year(Anno)->Semestre->Trimestre->Month
After I create MAT1, MAT2, YTD1, YTD2 calculated member, it becomes:
The MDX for creating these calculated members are given below:
CREATEMEMBER CURRENTCUBE.[PERIODO].[Periodo].[All].[YTD1]
AS SUM
(
BOTTOMCOUNT([PERIODO].[Periodo].[Anno].Members, 1)
),
VISIBLE= 1;
CREATEMEMBER CURRENTCUBE.[PERIODO].[Periodo].[All].[YTD2]
AS
SUM (
LASTPERIODS(
right(Tail([Periodo].[Periodo].[Months].Members).Item(0).Lag(12).properties(“Key”),2),
Tail([Periodo].[Periodo].[Months].Members).Item(0).Lag(12) VISIBLE= 1;
)
),
VISIBLE= 1;
great (y)