Forum Discussion
Insert a subsequent index value from same column into calculated column
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])))
You are welcome.
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
Hi,
It is very difficult to identify the problem this way. Please tell me in which specific row(s) of my calculated column formula is the result not correct i.e. is it 0 when in fact it should be 1.
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
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).
Here's a spreadsheet showing the which students don't match:
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.