MDX functions based on time

A bit of MDX to start the week, it feels good?

As you have already seen in the title, the purpose of this article is to share with you some time-based MDX functions that are very useful.

Time is an essential component for business analysis. Analysts interpret the state of the business now, often relative to what it was in the past, in order to understand what it might be in the future.

To support this, Analysis Services provides a number of time-based MDX functions, here are some of them:

  • PeriodsToDate
PeriodsToDate ([Level, [Member]])
Function PeriodsToDate returns all the members of the start of a period data up to a specified member including the affected member. The Level argument identifies the level of the hierarchy representing the time period over which the returned set should span, while the Member argument identifies the final element of the set.

Consider the following example:

SELECT {([Measures]. [Reseller Sales Amount])} ON COLUMNS, {PeriodsToDate ([Date]. [Calendar]. [Calendar Year], [Date]. [Calendar]. [Month]. [April 2002]) } ON ROWS FROM [AdventureWorksCube];


In the previous query, you used the function PeriodsToDate to recover all the months in the year 2002 before and including the month of April.

If you want to calculate a cumulative value for all the periods for which data is recorded, you can replace the level argument with ALL.

PeriodsToDate ([Date]. [Calendar]. [(All)], [Date]. [Calendar] .CurrentMmember)
  • LastPeriods
LastPeriods (n [, Member])

Function LastPeriods returns a set of n members before or after (and including) a specified member of a time hierarchy. If a positive n value is supplied, the returned set includes the members preceding the member of interest. If a negative n is supplied, the returned set includes the members following the member of interest.

An example to understand well:

SELECT {([Measures]. [Reseller Sales Amount])} ON COLUMNS, {LastPeriods (3, [Date]. [Calendar]. [Month]. [January 2002])} ON ROWS FROM [AdventureWorksCube];


In this query, you used the function LastPeriods to retrieve the three month period preceding and including January 2002. Analysis services starts with the specified member, January2002 and treats this as period 1. This leaves n-1 or n-2 members to return Overall. Because n is a positive number, Analysis Services retrieves the two previous siblings from the January 2002 member to complete the set (November 2001 and December 2001).

  • ParallelPeriod
ParallelPeriod ([Level [, n [, Member]]])

To understand ParallelPeriod, consider the month of April 2003. This month is the fourth month of the 2003 calendar year. In a business strongly influenced by the annual cycle, you might compare the values ​​for that month to those for the month of April in a previous year.

  1. The first argument to the function identifies the level of the time hierarchy through which you want to identify the parallel period member. If no level is identified, the parent level of the current time element is assumed.
  2. The second argument of the function identifies how far from the identified level you want to go to retrieve the parallel member. If no value is provided, a value of 1 is assumed, indicating the previous period.
  3. The final argument of the function identifies the member for which the parallel period is to be determined. The position of this member relative to its ancestor in the specified level determines the item retrieved from the historical period. If no member is identified, the current time element is assumed.

An example to illustrate this:

WITH MEMBER [Measures]. [X] AS ParallelPeriod ([Date]. [Calendar]. [Calendar Year], 1, [Date]. [Calendar] .CurrentMember) .Name SELECT {([Measures]. [Reseller Sales Amount ]), ([Measures]. [X])} ON COLUMNS, {Descendants ([Date]. [Calendar]. [Calendar Year]. [CY 2003], [Date]. [Calendar]. [Month], SELF )} ON ROWS FROM [AdventureWorksCube];


In the previous query, the function ParallelPeriod identifies the parallel period of the previous year for each month of the calendar year 2003.

Function ParallelPeriod returns a member and the name of that member is returned with a new calculated member to verify that the appropriate member is identified.

  • Conclusion:

The use of time-based MDX functions against calendar hierarchies gives the appearance of time awareness. However, most time-based functions simply exploit the basic hierarchy structure to return the required set or member.



One thought on "MDX functions based on time"

  1. I love it when I come together
    ideas. Great blog, continue the good work!


Leave a Reply

Your email address Will not be published.

This site uses Akismet to reduce spam. Learn how your comment is processed.