Measures for Another Time Period
It is often useful to view the value of a given measure for an earlier time period, while viewing a later time period. As an example, you can define a calculated measure called UnitsSoldPreviousPeriod and use the following value_expression:
([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])
Because of how this measure is defined, it is meaningful only if you use the DateOfSale dimension on the other axis of the query. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold DateOfSale
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
...
Notice that the caption of the second column is based on the dimension used within the value expression, rather than the name of the calculated member that we defined. We can use the %LABEL function to provide a more suitable caption. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],%LABEL([MEASURES].[UNITSSOLDPREVIOUSPERIOD],"Units (Prv Pd)","")} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold Units (Prv Pd)
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
6 Jun-2009 17 22
7 Jul-2009 24 17
8 Aug-2009 30 24
...
These examples use a time-based level, because this kind of analysis is common for time levels. You can, however, use the same technique for data levels.
