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

null

After I create MAT1, MAT2, YTD1, YTD2 calculated member, it becomes:
null

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;

 

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MDX, SSAS (Analysis Service), Cube. Bookmark the permalink.

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

  1. Al-Hasan (আল-হাসান) says:

    great (y)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s