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];