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

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

result:

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.

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.