Question

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

  • 27 September 2023
  • 8 replies
  • 579 views
K
Rank
Userlevel 2
Badge +6
  • Kevin Seasoned Pigmenteer
  • Seasoned Pigmenteer
  • 14 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

Nathan
Rank
Userlevel 6
Badge +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

K
Rank
Userlevel 2
Badge +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.

 

Nathan
Rank
Userlevel 6
Badge +11

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

K
Rank
Userlevel 2
Badge +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])

 

Nathan
Rank
Userlevel 6
Badge +11

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

Looking for a solution for that

K
Rank
Userlevel 2
Badge +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!

K
Rank
Userlevel 2
Badge +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!

B
Rank
Badge

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

Reply


Ask your question here!

Terms & ConditionsCookie settings