There are several ways for calculating “To-Date” calculations the longer way. These normally include a more granular level of control and less reliance on default options. The two functions in this post are examples of that added control and functionality, not provided in the WTD, MTD, etc.. functions.

Last Periods returns a set of members that includes all the members prior to and the currentmember. This can be useful when calculating a rolling average or growth compared to an all-time metric. Positive and negative numbers can be used for the number of periods, making this a forward looking calculation as well. The syntax for Last Periods looks like this:

LastPeriods(6,[date].[calendar].[month].[February, 2010])

This will return the last six months in a set previous to and including Feb. 2010. If there are not six months previous in the cube, it will return all that it finds.

PeriodsToDate retuns a set of periods from the supplied level, starting with the first period it finds and ending with the supplied member, or current member depending on the call. If no parameters are supplied, PeriodsToDate will assume the CurrentMember and again, this will only work if your date dimension has only one hierarchy.

There are a couple of examples to consider:

This returns Jan 1st though Feb 20th of 2010

PeriodsToDate([date].[calendar].[calendar year],  [date].[calendar].[date].[February 20, 2010]  )

This returns January and February of 2010

PeriodsToDate([date].[calendar].[month], [date].[calendar].[date].[February 27, 2010] )

This returns January 1st through March 25th, 2010

PeriodsToDate([date].[calendar].[calendar quarter], [date].[calendar].[date].[March 25, 2010] )

You will use these calculations to provide the set s to be aggregated to calculate sums and additive measures across time rollups for different types of calculations.

As Always, post your questions on the BIDN forums!