I am building an attrition model based on month dimension and a dimension called ‘30 Day Cohort’. I am almost certain that I will run into a circular reference if I do not use the Previous function. However, because I am working with 2 dimensions I am unclear on how to reference the previous month and previous ‘30 Day Cohort’ dimension simultaneously. Is this possible? Can I combine multiple Previous functions? Is it possible to nest two Previous functions?
To start my model, I have “initialized” the metric with both the starting values in the first month and the forecasted new subscribers in the first ‘30 Day Cohort’ (0 in my case):
What I would like to do is calculate a formula on the value in the previous month and previous cohort and then do so recursively (this is why I think I need to use the Previous function). So for Oct 23 and cohort 1, I would want to do my calculation based on the Sep 23 and cohort 0 value (1,895); for Oct 23 and cohort 2, I would want to do my calculation based on Sep 23 and cohort 1 value (1,565); so on and so forth.
I can recursively get to a previous month or a previous cohort but just not in tandem with each other:
IFBLANK('E03 Initialize BOP Subscribers',PREVIOUS(Month)+'E03 Initialize BOP Subscribers')
IFBLANK('E03 Initialize BOP Subscribers',PREVIOUS('30 Day Cohort')+'E03 Initialize BOP Subscribers')
The previous function is really great so I’m hoping there is something simple I am missing here on how to write the accurate formula. Thank you in advance for your assistance!