Pigment is well suited for many use cases, and in some of them, you will need to use the value that was previously calculated (some examples of this can be found in cash flow, balance sheet & inventory planning, and ARR waterfall use cases).
Using a previously calculated value will lead you to a “circular reference” issue that is well known in the modeling world and on the classic tools. Here’s how to manage it in Pigment.
The example: Inventory Planning
Here are the details to note for the following inventory planning example:
- Beginning Inventory: previous month’s End Inventory
- Incoming Re-order: replenishment that is arriving in the company’s warehouse this month
- Outgoing Sales: Sales order leaving the warehouse
- End Inventory: Beginning Inventory + Incoming Re-order - Outgoing Sales
In Pigment, the ‘Beginning Inventory’ Metric is calculated from the ‘End Inventory’ Metric which means writing the formula End Inventory = Beginning Inventory + Incoming - Outgoing will result in a circular reference.
Using previous to do recursive calculations
To calculate the ’Beginning Inventory’ Metric, we will use the SELECT modifier:
‘End Inventory’[select: Month-1]
This will bring the value of End Inventory of the previous Month to Beginning Inventory.
However, if you try to type the End Inventory formula again (Beginning Inventory + Incoming Re-order - Outgoing Sales), it will still result in a circular reference!
In Pigment, only the function PREVIOUS() is recursive and will allow the calculation to work the way we want it to here:
Previous will return the value of the same Metric offset by a number (by default one).
The formula for ‘End Inventory’ Metric should be:
PREVIOUS(Month)+'Incoming Re-order'-'Outgoing Sales'
And there you go, you now have the calculation available in the example!