Question

# Is it possible to use Previous() simultaneously across multiple dimensions?

• 8 replies
• 575 views

Userlevel 2
+6
• Seasoned Pigmenteer
• 13 replies

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!

### 8 replies

Userlevel 6
+11

Hey

One use of previous() should be enough to avoid the circular ref. Then you can you [select:Dimension-1] to get the value of the other one

Userlevel 2
+6

Thank you for the reply.  Is this what you were suggesting?

`IFBLANK('E03 Initialize BOP Subscribers',PREVIOUS('30 Day Cohort')+'E03 Initialize BOP Subscribers'[SELECT: Month-1])`

When I attempt that it just adds the previous month to the current cohort.  I am trying to get both the previous month and previous cohort.  Perhaps I am not following what you are suggesting.

Userlevel 6
+11

try PREVIOUS('30 Day Cohort')[SELECT: Month-1]

Userlevel 2
+6

Thanks again for the reply.  Unfortunately I get the dreaded “Error: Formula is invalid” message.

This is what I am trying to do with the Previous function.  But as you can see from just using the Select modifier that the results are not recursive.

`IFBLANK('E03 Initialize BOP Subscribers','E03 Initialize BOP Subscribers'[SELECT: Month-1][SELECT: '30 Day Cohort'-1])`

Userlevel 6
+11

Indeed it seems I’m wrong, you can’t actually do that on the previous()

Looking for a solution for that

Userlevel 2
+6

Thanks Nathan!  If there is a simple solution that would be fantastic.

Where my head is at now is I created a metric that is a text value of the previous values of both dimensions.

I’m not very confident this will get me on the right path but essentially it is a “key” for me to look back (my poor attempt to replicate the Previous() function for two dimensions).  So for the beginning of period that is Oct 23 in the 1 dimension, I want to pull in the end of period calculation from Sep 23 and 0 dimension.  So on and so forth.  I think I’ll run into a problem with a circular reference but I’m taking it one step at a time.

I appreciate you looking at this and keeping my fingers crossed something comes to your mind.  Thanks again!

Userlevel 2
+6

I was able to create metric that “looks back” across two previous metrics to an initial starting point.  There is a lot going on in the calculation below but essentially I used the SHIFT function and a calculation to go to a starting point.  Just sharing in case anyone wants additional examples on how to use the SHIFT.

The SHIFT is really useful but unfortunately I don’t believe this gets me any closer to a recursive calculation.  The ultimate goal is to go back one month and one previous period in the other dimension and perform a calculation on that value and carry that new value forward.  All this allows is for me to go back to a starting point and perform calculations on just those initial values (i.e., no recursive calculations on the new results).

`IF('G04 Month Difference from Forecast Start' >= '30 Day Cohort'.Int,`

`'G03 Initialize BOP Subscribers'[BY CONSTANT: SHIFT(Month,-'30 Day Cohort'.Int), SHIFT('30 Day Cohort',-'30 Day Cohort'.Int)],`

`'G03 Initialize BOP Subscribers'[BY CONSTANT: SHIFT(Month,-'G04 Month Difference from Forecast Start'), SHIFT('30 Day Cohort',-'G04 Month Difference from Forecast Start')])`

If anyone has thoughts on how to do this recursively that would be much appreciated!

I have the same requirement to use PREVIOUS across two dimensions. Was there ever a solution provided?