Retrieve selected year data and same level data from previous year

Sometimes user comes with the requirement, in which they want to compare “this year data with the previous year’s data”, “this quarter data with the previous year’s same quarter data”, ”selected month data with the previous year’s same month data”, “selected day’s data with last year’s same day data”. This requirement is very general in most of the organizations as they want to analyze the trend of their business by comparing current data with previous one. We can solve such requirements by using some MDX functions. So let’s discuss this with some sample MDX queries.

Calculate selected year data and the previous year data:

Firstly we discuss on how to retrieve previous year data by using MDX and for that we can use MDX ParallelPeriod function.

Syntax of ParallelPeriod function is as follows;

ParallelPeriod( [Level_Expression [ ,Index [ , Member_Expression ] ] ] )

If you execute following MDX query it will return data for supplied year i.e. 2003 and previous year data i.e.2002. So basically we are using ParallelPeriod function in which we used “Calendar Year” level for getting Year level data and we have passed Index as 1 for getting last year.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Calendar Year]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003];

If you are going to pass a member from “Calendar Year” level directly (i.e. [Date].[Calendar].[Calendar Year].&[2003]) then you can achieve your requirement by using MDX LAG function also. Following is the sample MDX in which I have used LAG function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      [Date].[Calendar].CurrentMember.Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE  ([Date].[Calendar].[Calendar Year].&[2003]);

In some cases user wants to pass particular date and want to view selected date’s year data and previous year’s data. In such requirement you can use MDX ANCESTOR function. In the following MDX query we are passing Date and retrieving data as per year.

WITH 
  MEMBER [Measures].[Selected Year's Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      ).Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Year's Data]
   ,[Measures].[Previous Year's Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[August 10, 2003]);

Output of all above mentioned MDX queries are same;

Output:

Calculate selected quarter data and data from previous year's same quarter:

Sometimes user select particular date and they want to view the selected date’s Quarter data as well as previous year’s same quarter data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Quarter Data]” will return Quarter data of selected date whereas “[Measures].[Previous Year's same Quarter Data] “ will return data from previous year’s same quarter.

WITH 
  MEMBER [Measures].[Selected Day's Quarter Data] AS
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's same Quarter Data] AS
    (
      Ancestor
      (
        Cousin
        (
          [Date].[Calendar].CurrentMember
         ,Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).Lag(1)
        )
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Day's Quarter Data]
   ,[Measures].[Previous Year's same Quarter Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);

Let me explain above MDX; Calculating value of “[Measures].[Selected Day's Quarter Data]” is pretty simple as we are simply using Ancestor function for retrieving Quarter level data. But for calculating value of a measure [Measures].[Previous Year's same Quarter Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same quarter.

Calculate month level data and previous year's same month data:

Sometimes user select particular date and they want to view the selected date’s Month level data as well as previous year’s same month data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Month level Data]” will return selected day’s Month level data  whereas “[Measures].[Previous Year's same Month Data] “ will return data from previous year’s same month.  

WITH 
  MEMBER [Measures].[Selected Day's Month level Data] AS 
    (
      Ancestor
      (
        [Date].[Calendar].CurrentMember
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Previous Year's same Month Data] AS 
    (
      Ancestor
      (
        Cousin
        (
          [Date].[Calendar].CurrentMember
         ,Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).Lag(1)
        )
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Selected Day's Month level Data]
   ,[Measures].[Previous Year's same Month Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);

Let me explain above MDX query; Calculating value of [Measures].[Selected Day's Month level Data]” is pretty simple as we are simply using Ancestor function for retrieving Month level data. But for calculating value of a measure [Measures].[Previous Year's same Month Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. And After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same month.

Calculate data for selected date and same date's data from previous year: 

In some scenarios, user wants to compare transaction of selected date and same transaction on the previous year's same date. so for achieving such requirements you can write MDX query by using Ancestor function. Refer sample MDX query for the same.

WITH 
  MEMBER [Measures].[Transaction of Selected Date] AS 
    (
      [Date].[Calendar].CurrentMember
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Transaction of Previous Year's same Date] AS 
    (
      Cousin
      (
        [Date].[Calendar].CurrentMember
       ,Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Year]
        ).Lag(1)
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Transaction of Selected Date]
   ,[Measures].[Transaction of Previous Year's same Date]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].[September 1, 2003];

How to calculate YTD, QTD, MTD and WTD?

YTD:

YTD function returns a set of sibling members from the Year level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Ytd([Member_Expression ])

Example:

Let's discuss YTD function by using a sample query. If you execute following MDX query,it will return a set of sibling members from Year level starting with the first sibling member i.e. January 2003 and ending with the specified member i.e. September 2003.

WITH

  SET [Required Set of Months] AS

    YTD([Date].[Calendar].[Month].[September 2003])

SELECT

  {[Measures].[Reseller Sales Amount]} ON COLUMNS

 ,[Required Set of Months] ON ROWS

FROM [Adventure Works];

Output:

Let's have a look at another example.If you execute following MDX query, then query will return the cumulative SUM of “[Reseller Sales Amount]” from Jan 03 to Dec 03.

WITH 
  MEMBER [Measures].[Calculate YTD] AS 
    Sum
    (
      YTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate YTD]
  } ON COLUMNS
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];

Output:

If you observe the value of a measure “Calculate YTD” for a month of February 2003 then that is the SUM of “Reseller Sales Amount” of “January 2003 and February 2003”.Similarly the value of measure for a month of “December 2003” is SUM of “Reseller Sales Amount” from “January 2003 to December 2003”.

QTD:

QTD function returns a set of sibling members from the Quarter level as a given member, starting with the first sibling member and ending with the specified member.

Syntax: Qtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Quarter level (i.e. from 3^rd Quarter of year 2003 (Q3)) starting with first sibling member i.e. July 2003 and ending with the specified member i.e. September 2003

WITH 
  SET [Required Set of Quarters] AS 
    QTD ([Date].[Calendar].[Month].[September 2003]) 
SELECT 
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
 ,[Required Set of Quarters] ON ROWS
FROM [Adventure Works];

Let's have a look at another example.If you execute the following MDX query,the query will return the SUM of “Reseller Sales Amount”, aggregated over each quarter i.e. if you observe value of a measure “Calculate QTD” for “March 2003”, you will find that it is a sum of “January 2003, February 2003 and March 2003” as these members belongs to first Quarter. Similarly if you observe a value for “June 2003”, you will find that it is a sum of “April 2003, May 2003 and June 2003” as these members belongs to second Quarter.

WITH 
  MEMBER [Measures].[Calculate QTD] AS 
    Sum
    (
      QTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate QTD]
  } ON COLUMNS

,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];

MTD:

MTD function returns a set of sibling members from the Month level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Mtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Month level,starting with the first sibling member i.e. “September 1, 2003” and ending with the specified member i.e. “September 8, 2003”.

WITH 
  SET [Set Of Members] AS
    MTD([Date].[Calendar].[Date].[September 8, 2003])
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Set Of Members] ON ROWS
FROM [Adventure Works];

Let’s discuss one more example, If you execute following MDX query, you will get a value “$525,802.16” which is the SUM of “Internet Sales Amount” from “1^st July 2003” to “19^th July 2003”.

WITH 
  MEMBER [Measures].[Calculate MTD] AS 
    Sum
    (
      MTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate MTD]} ON COLUMNS
 ,[Date].[Calendar].[Date].[July 19, 2003] ON ROWS
FROM [Adventure Works];

WTD:

WTD function returns a set of sibling members from the Weeks level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Wtd([Member_Expression ])

Example:

To understand WTD in more proper way, modify “Calendar Weeks” hierarchy. Add “Date” level to the “Calendar Weeks” hierarchy and execute following MDX query.

WITH 
  SET [Output set of members using WTD] AS 
    WTD([Date].[Calendar Weeks].[Date].[September 8, 2003]) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Output set of members using WTD] ON ROWS
FROM [Adventure Works];

If you execute above MDX query,it will return a set of sibling members from Week level,starting with the first sibling member i.e. “September 7, 2003” and ending with the specified member i.e. “September 8, 2003”.

Let's have a look at another example;If you execute the following MDX query,it will return  “$114,368.59” which is the SUM of “Internet Sales Amount” from “16^th February 2003 to “21^st February 2003”.

WITH 
  MEMBER [Measures].[Calculate WTD] AS 
    Sum
    (
      WTD
      (
        [Date].[Calendar Weeks].CurrentMember
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate WTD]} ON COLUMNS
 ,[Date].[Calendar Weeks].[Date].[February 21, 2003] ON ROWS
FROM [Adventure Works];