The Prev and Next Functions will return a Member from the Time Dimension which is N periods before or after the current Time Member respectively.
Syntax: Prev(<Time Dimension>,<N>)
Next(<Time Dimension>,<N>)
These functions are used as operands in Context Dependent Formulas.
Examples:
We’ll define two new Formulas as follows:
· [Sales Prev 1 Period]=([Sales_Val],Prev([Years],1))
This Formula will calculate the Sales_Val of 1 period previous to the current period.
· [Sales Prev 2 Periods]=([Sales_Val],Prev([Years],2))
This Formula will calculate the Sales_Val of 2 periods previous to the current period.
· [Sales Next 2 Periods]=([Sales_Val],Next([Years],2))
Where [Sales_Val] is a Measure and [Years] is the Time Dimension.
Note: The count of periods (N) adjusts itself automatically according to the lowest level of the Time Dimension presented in the Pivot i.e. if the lowest level is Years then the count will refer to years and if it is Months the count will refer to months etc.
In Grid1 below, The Time Dimensions is presented in Years therefore the [Sales Prev 1 Period] value will be that of the previous year e.g. the 1997 row, will have the Sales_Val of 1996.
In Grid2 below, the lowest Time level is Quarters, therefore the [Sales Prev 1 Period] value will be that of the previous Quarter e.g. the 1997Q1 row will get the Sales_val of 1996Q4.
Tip: If the Previous Member is not presented in the Pivot, the Prev() function will return empty value and the ({Measure],Prev([Time Dimension],N)) will return zero value.
In Grid2 below, since the lowest Time level is Quarters, the [Sales Prev 2 Period] value will be that of the 2 Quarters previous to the current one e.g. the 1997Q2row will get the Sales_val of 1996Q4.
The Next Functions behaves similarly but looks forward to return Members.
The [Sales Next 2 Periods] values in Grid2 below will show for every row, the [Sales_Val] of 2 quarters ahead of the current row.
Note: Prev([Time Dimension], N) = Next([Time Dimension], -N)
Tip: The Prev and Next Functions make it possible to compare the same Measure in different time periods.
Example: [Growth] = ([Sales])/([Sales],Prev([Time Dimension],1)-1