MAT (Moving Annual Total), YTD (Year to date) calculation using BottomCount, Tail, Properties function in MDX

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:

CREATE MEMBER CURRENTCUBE.[PERIODO].[Periodo].[All].[MAT1]
AS Sum (
BOTTOMCOUNT([PERIODO].[Periodo].[Months].Members, 12)
),
VISIBLE= 1 ;
CREATE MEMBER CURRENTCUBE.[PERIODO].[Periodo].[All].[MAT2]
AS SUM(
LastPeriods (
12 ,
Tail([Periodo].[Periodo].[Months].Members).Item(0).Lag(12)
)
),
VISIBLE= 1 ;

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;