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 compared 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 members at the beginning of a period given up to a specified member including the concerned member. The Level argument identifies the level of the hierarchy representing the period over which the returned set must extend, while the Member argument identifies the final element of the set.

Take the following example:

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

result:

In the previous query, you used the function PeriodsToDate to retrieve every month in the 2002 year 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 the 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 provided, the returned set includes the members preceding the member of interest. If a negative n value is provided, the returned set includes 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 before and including January 2002. Analysis Services starts with the specified member, January2002, and treats this as the 1 period. This leaves n-1 or n-2 members to return as a whole. 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 ParallelPeriodconsider 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 could compare the values ​​for this month to those for the month of April in a previous year.

  1. The first argument of 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 element extracted from the historical period. If no members are 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];

result:

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

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:

Using time-based MDX functions against calendar hierarchies gives the appearance of an awareness of time. However, most time-based functions simply exploit the basic structure of the hierarchy 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!

    Reply

Leave a Reply

Your email address Will not be published. Required fields are marked *