Description
Returns the value of the previous cell of the current Metric in the iteration Dimension.
This function allows you to create single Metric iterative calculations.
For more information on how to create iterative calculations, see Iterative Calculation & Circular Referencing.
Syntax
PREVIOUS(Iteration Dimension s, Offset])
Arguments
| Argument | Type | Dimensions | Descriptions |
|---|---|---|---|
| Iteration Dimension (required) | Number | Any Dimension included in the Metric structure | Dimension of the current Metric to which you want to apply an offset. |
| Offset (optional) | Integer | No Dimension or same as the Metric | Number of cells to offset. The default value is: 1 |
Returns
| Type | Dimensions |
|---|---|
| Same as the current Metric | Same as the current Metric |
Examples
| Formula | Description |
|---|---|
PREVIOUS(Month) | Returns the value of the previous month value of the current Metric. |
PREVIOUS(Month,2) | Returns the value of the current Metric cell, from two months previous. |
Limitations
To avoid cell-level circular dependencies, Pigment restricts the use of the PREVIOUS function in some formula syntax.
-
Dimension modifiers on expressions that use the PREVIOUS function
The Dimension modifiers ADD, REMOVE, SELECT, and BY cannot be applied to an expression containing a PREVIOUS function. For example, you can’t use this formula:PREVIOUS(Month)dBY: Month.Quarter]
The PREVIOUS function cannot be used inside a BY modifier, for example:metricmBY: PREVIOUS(Month)] - Combining Multiple Dimensions to Offset
It is not possible to combine multiple PREVIOUS() functions using different Dimensions to offset in a given formula.
For example, you can’t use this formula:PREVIOUS(Month) + PREVIOUS(Product)
- Advanced Function Compatibility
The PREVIOUS function is incompatible with some of the other functions available in Pigment.
These include:
- CUMULATE / DECUMULATE / SPREAD
- ISBLANK/ ISNOTBLANK
- IFDEFINED
- IRR / XIRR/ NPV
- MOVINGSUM / MOVINGAVERAGE
- DOUBLE_EXPONENTIAL_SMOOTHING / SIMPLE_EXPONENTIAL_SMOOTHING
- FORECAST_LINEAR, FORECAST_ETS, SEASONAL_LINEAR_REGRESSION
- DAYSINPERIOD, PRORATA, DAYSINPERIODWITHHOLIDAYS, PRORATAWITHHOLIDAYS
- NETWORKDAYS
- RANK
- filtering either with FILTER or applying a boolean Metric
For example, you can’t use this formula:CUMULATE(PREVIOUS(Month), Product)
- Offset
The offset parameter needs to be positive (negative offsets are ignored and considered blank). For example,PREVIOUS(Month, -1)will be ignored.
- Performance
Formulas using the PREVIOUS function don’t benefit from some optimizations in Pigment engine. As a best practise, avoid using thePREVIOUS()function unless necessary.
For more context around limitations of using PREVIOUS(), see Iterative Calculation & Circular Referencing.
See Also
Excel: no equivalent
Related articles: