Forum Discussion

Betsy's avatar
Betsy
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper IV
5 years ago

Hi Ashish_Mathur 

 

I'm having trouble with this formula when looking at real data. I made a dummy subset of this data. When I have 419 students, with 4 terms each worth of enrollment, I should see an enrolled next term count of 967, but instead the count shows 873, so 94 students are not being counted as enrolled (or 1). From what I can tell most of these (75) that are calculated as 0 rather than 1 are from enrolled_next_term for the first term (23/index 1) and the rest (19) are from the second term (44/index 2). The third term (79/index3) is counting correctly showing 301 students enrolled_next_term. 

 

I'm not sure that MIN(dummy_data[enrolled]) might not be functioning as expected? We want to just look up the value of enrolled at the >earlier[start_date] or [term] (I don't think we need both) somehow?

 

Thanks again! I cannot for the life of me figure out why this isn't working the same on the two sets of data.

 

Betsy

 

dummy_data2 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

In cell F2 of the MS Excel file, i wrote this formula and copied down

 

=MINIFS($D$2:$D$1677,$A$2:$A$1677,A2,$C$2:$C$1677,">"&C2,$B$2:$B$1677,">"&B2)

 

The total of this column is 873 which is exactly what my DAX formula returns as well

  • Betsy's avatar
    Betsy
    Icon for Helper IV rankHelper 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
      Icon for Helper IV rankHelper 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
        Icon for Super User rankSuper User
        5 years ago

        Hi,

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