Time-Aware MDX Functions
Some MDX functions, such as the Ytd() function, are time-aware, meaning they understand time logic and the various levels of a Time dimension. The Ytd() function returns a set of periods (members) from a specified member level in a Time dimension that has the same ancestor in the Year level, starting with the first period and ending with a specified member. If the specified member is 5/1999, the ancestor is the year 1999 and the Ytd() function would return all the months (the specified member level) starting with the first period (1/1999) and ending with the specified member (5/1999):
{1/1999,2/1999,3/1999,4/1999,5/1999}
The Ytd() function has a single, optional parameter—Member—which defaults to the current member of the Time dimension you're interested in. If you have only one Time dimension in your cube, you can omit the Member parameter; however, if you have more than one Time dimension, you must specify this parameter to tell Analysis Services or OLAP Services which dimension contains the set of members you want.
The Ytd() function is a shortcut to another function, PeriodsToDate(), which returns a set of periods (members) from a specified level, starting with the first period and ending with a specified member. Other shortcuts to PeriodsToDate() are Qtd(), Mtd(), and Wtd(). These functions are all set functions, because they return a set of periods. Another set function is LastPeriods(), which returns a set of members before and including a specified member. The syntax for the LastPeriods() function is
LastPeriods(Index [, Member])
As with the Ytd() function, if you don't specify Member, the LastPeriods() function defaults to Time.CurrentMember. If Index is positive, the LastPeriods() function returns the set of index members ending with the Member parameter you specified and starting with the member that is the Member parameter's Index value minus 1. In other words, if Time.CurrentMember is 5/1999 and Index is 3, you receive a set of three periods, starting 2 (that is, 3 minus 1) periods before the current one and ending with the current one:
{3/1999,4/1999,5/1999}
If your current member is Q2/1999, the LastPeriods(3) function returns the set
{Q4/1998,Q1/1999,Q2/1999}
Note that Index can be negative, in which case the returned set starts with Member and the set's size is the absolute value of Index. If Index is 0, LastPeriods() returns an empty set.
You usually use set functions in conjunction with other MDX functions, such as Avg() and Sum(). For example, to calculate year-to-date total sales, you would use the Ytd() and Sum() functions in the formula
Sum({Ytd()} , ([Measures].[Sales]))
To calculate year-to-date average stock, you would use the Ytd() and Avg() functions:
Avg({Ytd()} , ([Measures].[Stock]))
For more information about where to use these types of calculations, see the sidebar "The Right Place for Calculated Members."
Unlike the set functions, which return sets of members as the result, other time-aware MDX functions return just one member. One such MDX function is ParallelPeriod(), which returns a member from a prior period in the same relative position as a specified member. ParallelPeriod's syntax is
ParallelPeriod([Level[, Numeric_Expression[, Member]]])
Prev. page
1
[2]
3
next page 
