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 IQVIA (www.iqvia.com). Fields of expertise are C#,BI Reporting (MSBI, Microstrategy, Excel, ASP.NET Dashboards etc.) , ETL, Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MDX, SSAS (Analysis Service), Cube. Bookmark the permalink.

1 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.