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 these will vary. Students will be repeated if we have received more than one term for them, and students will have different term starts. Some data will be missing, but I don't think we will "skip" a term. Data is like the following (with index added):

 

What I would like to do is calculate an enrolled_next_term and enrolled_next_year value. So for Index 1 for a student enrolled_next-term equals the value for enrolled at Index 2, and enrolled_next_year = enrolled for index 3. And for all index 2 rows, enrolled_next term = enrolled at index 3 value...and so on. I'm guessing a calculated column is the way to do this (either query or dax is fine)? Another way to look at it: For Fall 2019 rows, the enrolled_next_term = Spring 2020 row's enrolled value, For Spring 2020 rows enrolled_next_term will be Fall 2020 row's enrolled value. 

 

Hope to have something like this as the resulting output:

 

 

Having a hard time coming up with the syntax for this. Thanks so much for your help!

 

Betsy

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

12 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Share the download link of the Excel file.  In that file, please show the expected result as well.

  • Betsy's avatar
    Betsy
    Helper IV
    5 years ago

    Hi Ashish,

     

    Ashish_Mathur Here's the example file I posted above, with the expected result in the enrolled_next_term and enrolled_next_year columns.Google sheets file 

     

    Thanks for looking at this!

     

    Betsy

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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

        Hi Ashish,

         

        Ashish_Mathur Thanks so much. Works perfectly. I used your formula as an example and used index +1 to produce enrolled_next_year too:

         

        enrolled_next_year = CALCULATE(MIN(dummy_data[enrolled]),FILTER(dummy_data,dummy_data[student]=EARLIER(dummy_data[student])&&dummy_data[index]>EARLIER(dummy_data[index]) +1 &&dummy_data[term]>EARLIER(dummy_data[term])))
         
        Thanks again for taking your valuable time to help me. I really appreciate it!
         
        Betsy