In Microsoft terms, the Level parameter takes the ancestor of Member, and the Numeric_Expression parameter is the sibling of this ancestor minus 1; the function returns the parallel period of Member among the descendants of that sibling. For example, ParallelPeriod(Year,1,[05/2000]) returns [05/1999]: The ancestor is [2000], minus 1 is [1999], and because [5/2000] is the fifth child of [2000], the function returns the fifth child of [1999], which is [5/1999].
As with the other time-aware functions, the Member parameter's default value is the current member of the Time dimension. However, if you specify Level, the default value is the current member in the dimension to which Level belongs. The default value of Numeric_Expression is 1, and the default value of Level is the level of the parent of Member. If your current time member's level is Month, to get the parallel month of the previous year, you could use
ParallelPeriod([Time].[Year])
If you want to calculate the growth in sales this year versus last year, you could use the calculation
([Measures].[Sales],[Time].CurrentMember) -
([Measures].[Sales], ParallelPeriod ([Time].[Year])
Two other functions that return one Time dimension member are ClosingPeriod(), which returns the last sibling among the descendants of a member at a specified level, and OpeningPeriod(), which returns the first sibling among the descendants of a specified level (optionally for a specified member).
You can also create a set as a range of periods; just specify two periods from the same level as endpoints with a colon between them, like a range in Excel. For example, the range {[2/1999]:[5/1999]} includes 2/1999, 3/1999, 4/1999, and 5/1999. Companies commonly need to calculate a rolling year, the total of the last 12 months (or last 4 quarters). To calculate a rolling year, people often use the formula
Sum(LastPeriods(12), [Measures].[Sales])
Although this formula works fine for a Month level, the LastPeriods(12) calculation doesn't work at any other level. You'd have to create a separate formula to calculate the rolling year based on Quarter, Day, or even Year. Based on what you've learned about MDX time functions, you can define this more generic calculation:
Sum({ParallelPeriod([Time].[Year],1).NextMember :
[Time].CurrentMember},[Measures].[Sale])
This calculation is independent of the number of level members in a year, and it returns a correct range at each level.
Several other MDX functions aren't time-specific but are frequently used in conjunction with a Time dimension. These functions include Head(), which returns the first specified number of elements in a set; Tail(), which returns a subset from the end of a set; Lag(), which returns the member that is a specified number of positions before a specified member along the dimension of the member; and Lead(), which returns the member that is a specified number of positions after a specified member along the dimension of the member.
If your Time dimension includes only periods for which you have data and the last period in your dimension is the last period for which you have data, the following MDX query always returns the past 3 months of data in the columns axis:
SELECT {Tail([Time].[Month].Members,3)} ON Columns ...
And the following query slices the data for the most recent quarter:
WHERE (Tail([Time].[Quarter].Members,1).Item(0))
Wealth of Functions
Analysis Services and OLAP Services let you declare any number of dimensions as Time dimensions and let you use a wealth of time-aware functions to work with these dimensions. These capabilities make reporting and analysis easier for both the developer and the end user.
End of Article
Prev. page
1
2
[3]
next page -->
