MDX YTD Calculated Member for current and parallel period

Question

Trying to build a calculated member using our date hierarchy for use in Tableau.
Hierarchy is as follows:
Fiscal Year
Fiscal Season
Fiscal Quarter
Fiscal Month
Fiscal Week
Date

To help our users with their analysis on dashboards, we're looking to build a Tableau set including the following members for them to toggle through: Yesterday, Week to Date, Month to Date, Season to Date, and Year to Date.

We need to be able to show Sales $ and Sales $ LY(measure is defined as parallel period) together and be able to toggle through the different "to date" values. The this year portion is fine, but creating a calculated member that works appropriately for the LY measure too has been problematic. We need to be able to pivot between the periods for dashboards rather than create individual measures for each period TY and LY.

Here are the current definitions we have out there:

Yesterday: [DATE].[Time].defaultmember

WTD: [DATE].[Time].defaultmember.parent

MTD: [DATE].[Time].defaultmember.parent.parent

STD: [DATE].[Time].defaultmember.parent.parent.parent

YTD: [DATE].[Time].defaultmember.parent.parent.parent.parent

Any thoughts on how to modify these so that the LY measure reflects the same "to date" period rather than the full period last year?

if it is saying 10 Jan 2006 on a row cell then you would like in the cell next to it the aggregation of (1 Jan 2007 - 10 Jan 2007) ? — whytheq

whytheq whytheq · Accepted Answer · 2015-04-17T08:01:03

Count the number of days completed this year and then use the Head function to get the same set of dates from the previous year - aggregate that set together.

Here is an illustration against the AdvWrks cube:

WITH 
  SET [DaysThisYear] AS 
    Descendants
    (
      [Date].[Calendar].[Calendar Year].&[2007]
     ,[Date].[Calendar].[Date]
    ) 
  MEMBER [Measures].[posOfCurrentDate] AS 
    Rank
    (
      [Date].[Calendar].CurrentMember
     ,Descendants
      (
        [Date].[Calendar].Parent.Parent.Parent.Parent
       ,[Date].[Calendar].[Date]
      )
    ) 
  MEMBER [Measures].[prevEquivMTD] AS 
    Sum
    (
      Head
      (
        Descendants
        (
          [Date].[Calendar].Parent.Parent.Parent.Parent.PrevMember
         ,[Date].[Calendar].[Date]
        )
       ,[Measures].[posOfCurrentDate]
      )
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Measures].[posCurrentYear] AS 
    [Date].[Calendar].Parent.Parent.Parent.Parent.Member_Caption 
SELECT 
  {
    [Measures].[posCurrentYear]
   ,[Measures].[posOfCurrentDate]
   ,[Measures].[Internet Sales Amount]
   ,[Measures].[prevEquivMTD]
  } ON 0
 ,
    [Date].[Calendar].[Date].&[20050111]
  : 
    [Date].[Calendar].[Date].&[20080611] ON 1
FROM [Adventure Works];

This is the above as a single measure:

WITH 
  MEMBER [Measures].[Internet Sales Amount prevEquivMTD] AS 
    Sum
    (
      Head
      (
        Descendants
        (
          [Date].[Calendar].Parent.Parent.Parent.Parent.PrevMember //previous year
         ,[Date].[Calendar].[Date]
        )
       ,Rank
        (
          [Date].[Calendar].CurrentMember
         ,Descendants
          (
            [Date].[Calendar].Parent.Parent.Parent.Parent //current year
           ,[Date].[Calendar].[Date]
          )
        )
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Sales Amount prevEquivMTD]
  } ON 0
 ,
    [Date].[Calendar].[Date].&[20050111]
  : 
    [Date].[Calendar].[Date].&[20080611] ON 1
FROM [Adventure Works];

Unfortunately it is tied to the measure Internet Sales Amount so not very generic. Generic calcs such as prev equiv mtd or Year on Year growth are usually added to a helper Time Caculation helper dimension that is independent from the Date dimension.

MDX YTD Calculated Member for current and parallel period

1 Answers