A common modeling need when building a Pigment Application is to have an iterative calculation. An iterative calculation in Pigment is where a formula iterates over all the items in a Dimension, performing a specified calculation on each item in sequence.
To support iterative calculations, Pigment provides two functions:
 PREVIOUS. To create an iterative calculation while referencing the same Metric.
For more information, see PREVIOUS function.  PREVIOUSBASE. To create an iterative calculation while referencing another Metric.
For more information, see PREVIOUSBASE function.
About BlockLevel Circular Dependencies
The Pigment modeling engine computes Blocks in sequence, meaning one after the other. Each time a Block is updated, all of its dependent Blocks, or more specifically the Blocks using this Block in their formulas, are automatically recalculated. Creating a loop of Block dependencies could lead to an infinite loop of calculations.
For that reason, each time a formula is created or edited, the Pigment engine checks if the new formula will create a Blocklevel circular dependency. If this happens, the formula will be rejected.
Multiple Block Circular Dependency
Let’s say a model has two Metrics: Metric A and Metric B.
Metric A depends on Metric B with the formula: Metric A = 'Metric B' + 1
If a formula is input on Metric B referencing Metric A ( Metric B = 'Metric A' + 1), it will be rejected. This is because, without any special function, this creates a circular dependency, which isn’t allowed.
Single Block Circular Dependency
Similarly, if you input a formula on Metric A referencing Metric A ( Metric A = 'Metric A'iSELECT: Month  1] + 1
), this will be rejected. For the same reason, Pigment prevents Members from selfreferencing Blocks in the formula.
Comparing to Excel: Why can iterative calculations be done in Excel but not in Pigment without special functions?
Pigment is a multidimensional database that works with Blocks not with cells. As a result, it can occur that you have a circular reference on a Block level but not on a cell level.
Because a spreadsheet's circular dependency detection is more granular, reproducing Excel formula logic in Pigment can be challenging.
When circular dependency errors occur, Pigment offers two special functions to resolve them by adjusting the modeling, PREVIOUS () and PREVIOUSBASE().
Single Block Iterative Calculation
As mentioned in the Single Block Circular Dependency example, a standard Pigment formula doesn’t allow selfreferencing within the same Block.
For example, you can’t use the formula: 'Metric A' = 'Metric A' SELECT: Month] + 1
However, the PREVIOUS function allows you to do this.
The PREVIOUS function always refers to the Metric in which the formula is written, using a specified positive integer offset. The modeler must specify which Dimension of the Block to offset. The time Dimension is the most common choice, but not the only option.
Examples of how to use the PREVIOUS function
Increment a metric based on the value of the Previous Month
This example shows how to go through each item in the Month Dimension and increase the value of the previous month's item by 1. The data in Jan 24 is entered as manual input.
To do this, use the formula: PREVIOUS(Month) +1
Calculate a Cash Flow Balance
An example of a common financial use case is a cash flow statement. You can use the PREVIOUS function can be used to calculate a cash flow balance.
To do this, use the formula: Cash = PREVIOUS(Month) + Income  Expense
Simple Inventory Planning
In the inventory example below, the following details are available:
 Beginning Inventory: Inventory for the end of the previous month
 Incoming Reorder: Restock of inventory arriving in the company’s warehouse this month
 Outgoing Sales: Sales orders leaving the warehouse
 End Inventory: Beginning Inventory + Incoming Reorder  Outgoing Sales
In Pigment, the Beginning Inventory Metric is calculated from the End Inventory Metric. If you write the following formula, it results in a circular reference:End Inventory = Beginning Inventory + Incoming  Outgoing
To calculate the Beginning Inventory Metric, use the SELECT modifier:
End Inventorymselect: Month  1]
.
This brings the value of End Inventory from the previous month to Beginning Inventory.
However, if the formula for End Inventory formula is entered again, it still results in a circular reference:
Beginning Inventory + Incoming Reorder  Outgoing Sales
This is because the Beginning Inventory Metric contains End Inventory
in its formula. Pigment detects it as a circular reference and restricts a Metric to reference itself in the formula.
In Pigment, only the PREVIOUS function is recursive, and it allows the calculation to work as needed. The PREVIOUS function returns the value of the same Metric offset by a number, which by default is one.
To make this calculation work in Pigment, and to avoid the circular reference, the formula you need for the End Inventory Metric is:
PREVIOUS(Month)+ Incoming Reorder  Outgoing Sales
Dynamic Offset
The PREVIOUS function also has a second optional parameter. This parameter specifies the number of Dimension items (not always periods) the offset needs to be done.
It must be a positive constant integer, or a Metric of type Integer defined on the same Dimensions as the Metric. A negative value is automatically ignored.
It must be a positive constant integer or a Metric of type Integer defined on the same Dimensions as the Metric. A negative value will be ignored automatically.
Limitations and notable behaviour when using PREVIOUS
To avoid celllevel circular dependencies, Pigment restricts the use of the PREVIOUS function in the formula language 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)sBY: Month.Quarter]
The PREVIOUS function cannot be used inside a BY modifier, for example:metricdBY: 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
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 practice, avoid using the PREVIOUS() function unless necessary.
MultiBlock Iterative Calculation
For some use cases, the PREVIOUS function can’t prevent circular references, and instead you need to use the PREVIOUSBASE function.
Consider the previous Simple Inventory Planning example, and the Incoming Reorder is defined as an expression of End Inventory.
For instance, if the Incoming Reorder is set to fill the stock by 200 units based on last month's endofperiod stock, and the formula in the Incoming Reorder Metric is 200  'End Inventorylselect: Month  1]
, the formula won’t allow it.
The dependency diagram shown below wouldn’t be possible:
Concept
To solve this problem you can use Iterative Calculation. The idea is to declare a list of Metrics on which the standard circular reference detection is disabled.
An Iterative Calculation configuration is made up of three things:
 a base Metric
 an iteration Dimension
 a list of allowed Metrics
When you create an iterative calculation configuration, all allowed Metrics can reference the base Metric in their formula on the condition that they use the PREVIOUSBASE
function.
PREVIOUSBASE
returns the base Metric value, which has shifted by one period along the iteration Dimension. Using the PREVIOUSBASE
function is equivalent to writing the formula 'Base Metric'/SELECT: 'Iteration Dimension'  1]
but it doesn’t trigger the circular reference error.
In the list of allowed Metrics, you must select all Metrics which are part of the dependency cycle.
Let’s say you want to build this cycle:
In this example, your base metric is:
 End Inventory
This is because the model is calculating the Beginning Inventory Metric based on the previous time period of the End Inventory Metric.
The list of allowed Metrics should only contain:
 Beginning Inventory
 Incoming ReOrder
Limitation:
 Allowed Metrics cannot use the PREVIOUS function.
 The maximum number of allowed Metrics is 10.
 All Metrics in the iterative calculation configuration, that is, base and allowed Metrics, need to have the iteration Dimension in their structures.
Create an Iterative Calculation
This example explains how to build out an inventory plan in your Application using iterative calculation. For this example, we use the inventory use case described in MultiBlock Iterative Calculation above.
 In Application Settings, click Calculations, and then click Add an iterative calculation.
 In the Iterative calculation pane, select the following:
a. Base Metric. End Inventory Metric
b. Iteration Dimension. Month Dimension
c. Allowed Metrics. All Metrics needed in the iterative cycle.  Click Save.
 In the following Metrics, enter the following formula:
 Beginning Inventory:
PREVIOUSBASE()
 End Inventory:
'Beginning Inventory'+'Incoming Reorder''Outgoing Sales'
Next you need to make the Incoming Reorder Metric dependent on the previous month’s End Inventory Metric.
 In the Incoming Reorder Metric, input the following formula:
200>Add: Month]  PREVIOUSBASE()
You’ve created a model where the Incoming Reorder and Beginning Inventory Metrics rely on the End Inventory Metric for their calculations.
Further Information
How does it work ?
When you create an iterative calculation configuration, the Pigment engine consolidates the formulas of the Metrics involved into a single base formula to resolve circular dependencies. This base formula, which incorporates the logic of all the formulas in the cycle, is then used to compute the base Metric.
In the Inventory Planning example, the base Metric formula is:
'Beginning Inventory' + 'Incoming Reorder'  'Outgoing Sales'
Automatically, this formula transforms to include the Beginning Inventory and Incoming Reorder formulas. The base formula is:
(PREVIOUS(Month)) + (200IAdd: Month]  PREVIOUS(Month))  'Outgoing Sales'
The PREVIOUSBASE function is replaced by the PREVIOUS function defined for the specified iteration Dimension.
Optimization
Because the base Metric formula combines all allowed Metrics into one big formula, it's best for performance to omit any unnecessary Metrics from the list of allowed Metrics.
In the example below, the Outgoing Sales Metric is included in the list of allowed Metrics  but isn’t actually used. Any Metrics with the status Not Part of a Circular Dependency can be removed from the list of allowed Metrics.
The Active icon indicates whether the formulas of the different Blocks generate a cycle or not. If the iterative calculation configuration has an Inactive icon, it means it can be safely removed.
Debugging Formula Errors
When using iterative calculation configuration, the Pigment engine builds a base formula that would be subject to the same limitations as the standard PREVIOUS function. As a result, it’s possible to write three valid formulas in three different Metrics but generate an invalid base formula.
For example:
 End Inventory Metric:
'Beginning inventory' + 'Incoming reorder'
 Beginning Inventory Metric:
PREVIOUSBASE()
 Incoming Reorder Metric:
'Beginning inventory'nREMOVE: Month]
When this happens, the base Metric formula displays an error, and the error message indicating that the different formulas of the Block cycle are incompatible.
The Initial Error section identifies the formula error encountered by the base formula.
With the above example the merged base formula would be :
PREVIOUS(Month) + PREVIOUS(Month)>REMOVE: Month]
This base Metric formula will fail because the REMOVE Dimension modifier is not allowed on the PREVIOUS function result.