Returns: A set.
Syntax
>>-PeriodsToDate--(--+------------------------------------------+--)->< '-LevelExpression--+---------------------+-' '-,--MemberExpression-'
The LevelExpression and MemberExpression parameters are now optional, as shown. Prior to version 8.5, both were required.
Parameters
- LevelExpression
- Specifies the level of the particular time period that all returned
members must belong to. For example, if you specify Time.Week, PeriodsToDate will
return only members from the same week.
If you omit LevelExpression, then you must also omit MemberExpression; when you do so, the function uses Time.CurrentMember and the level of its parent as defaults.
- MemberExpression
- Specifies the chronologically last member of the Time hierarchy
that is to be returned.
If you omit this parameter, the function uses Time.CurrentMember by default.
Description
Use the PeriodsToDate function to return a set of members of the Time hierarchy within a certain time period, up to and including a particular member. For example, you might use the function to return the set of days in the week up to and including December 31, or the set of weeks from this year up to and including the current week.
Although the levels and members of the Time hierarchy make the most sense as arguments to the PeriodsToDate function, you can use the levels and members of any hierarchy, provided that the hierarchy is structured such that every sub-hierarchy has the same structure as its siblings and cousins. For example, if you provide as an argument a member that has three children but only two nephews, the function will likely cause an error or otherwise behave unexpectedly. In general, the PeriodsToDate function returns a set of members that are of the same level and have a common ancestor at the level that is specified by LevelExpression.
Example
The following example uses the function to retrieve the sales amount and period-to-date sales amount for the members of the Time hierarchy from the year level to the month level for the year 2002. In this example, the targeted period is year.
WITH
MEMBER [Price Analysis].[Measures].[Sales Amount (PTD)]
AS Sum(PeriodsToDate([Price Analysis].[Time].[Calendar Year Level],
[Price Analysis].[Time].CurrentMember),
[Price Analysis].[Measures].[Sales Amount])
SELECT {[Price Analysis].[Measures].[Sales Amount],
[Price Analysis].[Measures].[Sales Amount (PTD)]} ON AXIS(0)
, {Descendants([Price Analysis].[Time].[All Time (Calendar)].[2002],
[Price Analysis].[Time].[Calendar Month Level (Price Analysis)],
SELF_AND_BEFORE)} ON AXIS(1)
FROM [Price Analysis]| Time | Sales Amount | Sales Amount (PTD) |
|---|---|---|
| 2002 | 871391.1 | 871391.1 |
| 1 | 314832.92 | 314832.92 |
| 1 | 114644.29 | 114644.29 |
| 2 | 137392.25 | 252036.54 |
| 3 | 62796.38 | 314832.92 |
| 2 | 234051.3 | 548884.22 |
| 4 | 76407.44 | 391240.36 |
| 5 | 65171.51 | 456411.87 |
| 6 | 92472.35 | 548884.22 |
| 3 | 245123.78 | 794008 |
| 7 | 97459.94 | 646344.16 |
| 8 | 73426.12 | 719770.28 |
| 9 | 74237.72 | 794008 |
| 4 | 77383.1 | 871391.1 |
| 10 | 77383.1 | 871391.1 |
| 11 | 871391.1 | |
| 12 | 871391.1 |