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