I was working on Headcount planning and in order to be able to calculate attrition, I used iterative calculation functionality. With that I was able to calculate Attrition by multiplying attrition rate to ending (beginning) headcount, which is, in its turn, calculated as Beginning Headcount plus new hires minus attrition.

Now I want to add Backfills to the calculation, so Ending Headcount will be Beginning Headcount plus new hires minus attrition plus backfills. I am calculating backfills as % of Attrition, and Backfills is also a part of Iterative Calculations. 

The thing I want to do, but cannot, is being able to instead of current period’s attrition get 2 months’ prior attrition and multiply it by backfill rate. So, if for instance Jan 25 attrition is 100 FTEs, my Mar 25 backfills should be 100 * 90% (Backfill rate) = 90 FTEs. So, instead of having 90 FTEs backfills in Jan 25, I would like to have it in Mar 25.

I tried to put tSELECT: Month - 2] in the formula for Backfills metric:
(Attrition_Metric * Backfills_Rate)sSELECT: Month - 2]
 

But this throwing an error saying that I cannot use SELECT in the metric which is a part of Iterative Calculation. How can I get lagged Backfill values?

For reference formulas in my metrics are following:

Beginning_FTEs: PREVIOUSBASE()
Ending_FTEs: Beginning_FTEs - Attrition_Metric + NewHires_Metric + Backfills_Metric
Attrition_Metric: PREVIOUSBASE() * Attrition_Rate
Backfills_Metric: Attrition_Metric * Backfills_Rate

I want Backfills_Metric​​​​​​​ to be “Attrition_MetricrSELECT: Month - 2] * Backfills_Rate” or “(Attrition_Metric * Backfills_Rate)ASELECT: Month - 2]”