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])))

Betsy's avatar
Betsy
Helper IV
5 years ago

If you sum the enrolled column for index 2-4 (skipping index 1 which is the first term) $D421:$D1677 you get 967 which is what a calculated enrolled_next_term column should equal, since enrolled_next_term shifts the index ahead 1. MINIFS is not counting all of the enrolled that should be included in enrolled_next_term until you get to index 3 with enrolled_next_term equaling index 4 enrolled. Is the MIN in the original formula  performing that same minimal criteria calculation? (I don't really understand MIN or MINIFS in this context). 

Betsy's avatar
Betsy
Helper IV
5 years ago

Here's a spreadsheet showing the which students don't match:

 

mismatched rows 

 

Column F (enrolled_next_term for Index 1) should equal Column I (enrolled for index 2). I enrolled_next_column is generated from the formula in BI.

  • 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!