Forum Discussion

Betsy's avatar
Betsy
Helper IV
5 years ago
Solved

Insert a subsequent index value from same column into calculated column

Hi,   Haven't asked a question here for a long while. I have academic data that I am looking to calculate enrollment from term to term. Each subsequent term will have a higher term number, but thes...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works for enrolled next term

    =CALCULATE(MIN(Data[enrolled]),FILTER(Data,Data[student]=EARLIER(Data[student])&&Data[start_date]>EARLIER(Data[start_date])&&Data[term]>EARLIER(Data[term])))

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

I just do not understand.  May be someone else will help you.

Betsy's avatar
Betsy
Helper IV
5 years ago

Hi Ashish,

 

In case you are interested, and for anyone reading this in the future, it was the >EARLIER for any of the other fields or combination of fields you could use ([start_date], [term], or [index]) that was somehow not counting all of the values. 

 

In order to have enrolled_next_term = enrolled at terms 2-4, this formula using [index] works:

 

enrolled_next_term = CALCULATE(MIN(dummy_data2[enrolled]),FILTER(dummy_data2,dummy_data2[student]=EARLIER(dummy_data2[student])&&dummy_data2[index]=EARLIER(dummy_data2[index]) +1))
 
Instead of [index] > EARLIER([index]), substituted [index] = EARLIER([index]) +1
 
I don't know why > doesn't work and I think these two formulas should equal each other, but they do not yield the same result in this data anyway.
 
Thanks again!