A common modeling need when building a Pigment Application is to have a formula iterate over all the Items in a Dimension, using a calculation from one Item to calculate the next.

This page introduces and explains the PREVIOUS function, one of Pigment's tools for creating iterative calculations. This function is used within a single Block. However for multi-Block calculations, you need to use the PREVIOUSBASE function. See this usage article for more information on using PREVIOUSBASE.

About circular dependencies

The formula below is an example of a circular dependency within a single Block:

Metric A = 'Metric A''SELECT: Month - 1] + 1

It attempts to increase Metric A by one month-on-month, but would result in an error message, as Metric A cannot reference itself in the formula. Each time a formula is created or edited, the Pigment engine checks for circular dependencies, flagging errors with pop-up messages such as the below:

It does this to prevent an infinite loop of calculations. To make iterative calculations work, Pigment uses two tools, as shown in the table below:

The first of these is explored in this article. For the second, see the article on this link for guidance.

Single Block Iterative Calculation: PREVIOUS

When you need to refer to data within the same Metric, Pigment offers a special function: PREVIOUS. PREVIOUS returns the value of the previous cell of the current Metric in the iteration Dimension.

Using PREVIOUS means that the value of a given cell depends on the calculation of the previous one in that Metric. The Pigment engine therefore needs to calculate the final value of the previous cell in order to replace the PREVIOUS expression in the current cell.

For this reason, the engine computes the whole formula, including any syntax before and after the PREVIOUS expression, before moving on to the next cell. As the scope of iteration is the whole formula and Metric, any post-processing, such as filtering or certain other operations, should happen in another Metric that references the current one. See Special Cases below for an example.

You can also control whether PREVIOUS returns one item prior, or more than one. While PREVIOUS(Month) provides the cell value from the month prior, PREVIOUS(Month,2) provides the value from two months prior. This optional parameter is one by default but can be any positive integer. You can also use a Metric to provide dynamic offsets.

Below are examples demonstrating ways to use PREVIOUS.

Example: increment a Metric based on the value of the previous month

This example shows how PREVIOUS provides values for each item along the Month Dimension, increasing the value of the previous month's item by one, starting from a manual input of 10 in the Jan 24 cell. The formula applied is:

PREVIOUS(Month) +1

Example with dynamic offset: increment a Metric based on the value from two months prior

The PREVIOUS function also has a second optional parameter. This parameter specifies the number of Dimension items (not always time periods) to offset. 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. The formula applied is:

PREVIOUS(Month,2) +1

Example with other Metrics: calculate a cash flow balance

This example combines the PREVIOUS expression with other Metrics to calculate a cash flow balance over multiple periods. The formula is:

Cash = PREVIOUS(Month) + Income - Expense

Example showing circular dependency: plan inventory

In the warehouse inventory example below, the following details are available:

• Beginning Inventory: End inventory from the previous month
• Incoming Re-order: Restock of inventory arriving in the company’s warehouse this month
• Outgoing Sales: Sales orders leaving the warehouse
• End Inventory: Beginning Inventory + Incoming Re-order - Outgoing Sales

You could set up a formula based on the following:

End Inventory = Beginning Inventory + Incoming Re-order - Outgoing Sales

But the Beginning Inventory Metric itself requires the End Inventory Metric, using the SELECT modifier:

Beginning Inventory = End Inventory.

This results in a circular reference and error message, as the formula for End Inventory includes Beginning Inventory, whose formula in turn includes End Inventory. To resolve the fact that End Inventory needs to refer to data within itself, the formula has to use PREVIOUS, as only the PREVIOUS function is recursive:

End Inventory = PREVIOUS(Month) + Incoming Re-order - Outgoing Sales

Special cases

Certain operations are currently not compatible with PREVIOUS. See a full list on the PREVIOUS function page. Other operations, while compatible, have important implications.

In the below example using IFBLANK, the first row is a series of Booleans. The second and third rows use IFBLANK to populate the row with patchy data from a Block called ‘Wk sales data’, using PREVIOUS to fill any blank cells with the value from the previous Item. The difference between them is that the third row also contains e'Filter boolean'] at the end. The modeler’s aim is to replicate the second row but only where the Boolean is TRUE, leaving blanks everywhere else.  

However, the Pigment engine has returned blanks everywhere, including in cells marked TRUE. This is because the IFBLANK function is applied in its entirety cell by cell, as shown below:

1. Until Item WC 2023-12-18, the Pigment engine issues blanks in each cell as the Booleans are FALSE.
2. From Item WC 2023-12-25 onward:
   1. The Boolean is TRUE, so the filter is not applied.
   2. IFBLANK’s first argument finds a blank in ‘Wk sales data’.
   3. This triggers IFBLANK’s second argument PREVIOUS(Week), which finds a blank in the previous cell.
   4. BLANK is returned.

To avoid situations such as these, modelers should use the expression containing PREVIOUS in a separate Metric before applying a Boolean filter. In the above example, modelers should create a separate sales data Metric containing the formula from the second row. Then in this Metric they should apply the filter on that sales data Metric: ('Sales data Metric' c'Filter boolean']).

Tips for building with performance in mind

Datasets that include blanks allow for much faster computation, as Pigment is designed to handle sparsity. Given that the PREVIOUS function reduces the amount of blanks in your model, you can optimize performance by following these three tips while formula-writing:
 

1. Keep sub-expressions light.


Using densifying sub-expressions can increase computations in your formula. For example, in the following formula:

IF(ISBLANK(Metric_1), PREVIOUS(Month))

ISBLANK replaces blank cells with TRUE, increasing data density. You can rewrite this IF expression as an IFDEFINED expression, preserving the blanks and optimizing performance:

IFDEFINED(Metric_1,Metric_1,PREVIOUS(Month))

Try to use IFDEFINED rather than ISBLANK/ISNOTBLANK, as this works better with Pigment’s sparse engine.

2. Reduce the number of times you use PREVIOUS in a formula.

When using PREVIOUS in formulas, group expressions together and isolate PREVIOUS calculations where possible. Because of how PREVIOUS is executed in the Pigment calculation engine, it is best to isolate PREVIOUS within your formula. For example:

Previous(Month) * (A+B)

is more performant than:
Previous(Month) * A + Previous(Month) * B

Also if you have nested statements within your formula, such as:

IF(Month = "Jan", MetricA,
IF(Month = "Feb", MetricB,
...
IF(Month = "Dec", Previous(Month))

Bring the PREVIOUS component up higher:

IF(Month = "Dec", Previous(Month),
IF(Month = "Jan", MetricA,
IF(Month = "Feb", MetricB, ...

3. Minimize the number of Iterations to calculate.

As PREVIOUS is an iterating function, the higher the number of Items in the iterating Dimension, the longer the formula execution time. Also, the iterating Dimension has a maximum of 10,000 Items when using PREVIOUS. To mitigate this, you could leverage the List Subsets feature to reduce the iterating Dimension.

In the example below, a Calendar Subset called “2025-2026” cuts out all but two years of iterations from the company Calendar Dimension.

4. Use complex expressions sparingly within PREVIOUS

Avoid nesting complex expressions within the PREVIOUS function where possible, for example: 

PREVIOUS(Month, IF(<something>, 1))

Instead, consider patterns such as:

IF(<something>, PREVIOUS(Month))

PREVIOUS(Month) FILTER: <something>]

5. Use a constant offset in PREVIOUS where possible, rather than a dynamic offset

Formulas using a constant offset in PREVIOUS are more performant compared to formulas where a Metric is the offset. 

If you have to use a Metric as the offset, try to avoid adding Dimensions to it. For example, avoid constructs such as: 

Previous(Month, OffsetMetricvAdd: DimA])
 

Learn more

• PREVIOUS function
• PREVIOUSBASE function
• IFBLANK function