MDX Built-in Functions - Date Functions

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services - MDX Built-In Functions

Date functions allow aggregating and comparing data across multiple time periods. Commonly used date functions include:

Contents

LastPeriods

LastPeriods function returns a set of members prior to and including the current member. The syntax is LastPeriods(number of periods, Member). For example, the following expression returns November 2001 and December 2001:

LastPeriods(2,[date].[calendar].[month].[december 2001])


You can specify the positive or negative values for "number of periods", however, if you specify zero an empty set is returned. The next example returns December 2001 and January 2002:



LastPeriods(-2,[date].[calendar].[month].[december 2001])



If MSAS does not find the specified number of periods it will return what it does find. For example, if you specify 12 months and there are only 6 months prior to December 2001 it will return July through December. Although typically used for date dimensions this function also works for non-date dimensions; for example, the following returns "cable lock" and "classic vest":

LastPeriods(2,[product].[product model lines].[model name].[classic vest])

MTD

MTD stands for month to date and returns a set of members from the date and time dimension starting with the first date of the month up to the specified member. For example, the following expression will return January 1st through January 11th of 2003:

MTD([date].[calendar].[date].[January 11, 2003])


You have an option of calling the MTD() function without any parameters in which case it assumes the current date within the time dimension. However, this only works if your time dimension has only one hierarchy. The best practice is to specify the member you want to use with MTD. This function only works at the month level of the date and time dimensions.

PeriodsToDate

PeriodsToDate function returns a set of periods from a given level starting with the first period it finds and ending with the specified member. The syntax is PeriodsToDate(LEVEL, MEMBER). Neither argument is required. If neither parameter is supplied PeriodsToDate will assume the current member of the time dimension. This only works if your date and time dimension only has one hierarchy, however. For example, if the current member of the time dimension is January 3rd, 2004 then PeriodsToDate will return January 1st through January 3rd of 2004. If member isn't specified PeriodsToDate will assume the current member of date and time dimension as the member.



For example, the following expression returns January 1st through February 2nd of 2003:

PeriodsToDate([date].[calendar].[calendar year],  [date].[calendar].[date].[February 2, 2003]  )


The next example returns just the months of January and February of 2003: 

PeriodsToDate([date].[calendar].[month],  [date].[calendar].[date].[February 2, 2003]  )


The next expression returns April 1st and April 2nd of 2003:

PeriodsToDate([date].[calendar].[calendar quarter],  [date].[calendar].[date].[April 2, 2003]  )


WTD, MTD, QTD and YTD functions are shortcuts to the PeriodsToDate function that only work on week, month, quarter and year levels, respectively.

QTD

QTD stands for quarter to date and returns a set of members from the date and time dimension starting with the first day of the quarter up to the specified member. For example, the following expression will return January 1st through March 11th of 2003:

QTD([date].[calendar].[date].[March 11, 2003])


You have an option of calling the QTD() function without any parameters in which case it assumes the current date within the time dimension. However, this only works if your time dimension has only one hierarchy. The best practice is to specify the member you want to use with QTD. This function only works at the quarter level of date and time dimensions.

WTD

WTD stands for week to date and returns a set of members from the date and time dimension starting with the first day of the week up to the specified member. Week level is not as common as month, quarter and year levels, but is useful in some situations. For example, the following expression will return March 9th through March 11th of 2003 as long as there is a week level in the date dimension:

WTD([date].[calendar].[date].[March 11, 2003])


You have an option of calling the WTD() function without any parameters in which case it assumes the current date within the time dimension. However, this only works if your time dimension has only one hierarchy. The best practice is to specify the member you want to use with WTD. This function only works at the week level of date and time dimensions.

YTD

YTD stands for year to date and returns a set of members from the date and time dimension starting with the first day of the year up to the specified member. For example, the following expression will return January 1st through April 11th of 2003:

YTD([date].[calendar].[date].[April 11, 2003])


You have an option of calling the YTD() function without any parameters in which case it assumes the current date within the time dimension. However, this only works if your time dimension has only one hierarchy. The best practice is to specify the member you want to use with YTD. This function only works at the year level of date and time dimensions.
Retrieved from "http://sqlserverpedia.com/wiki/MDX_Built-in_Functions_-_Date_Functions"