Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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])))
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
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])))
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:
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
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.
Hi,
I just do not understand. May be someone else will help you.
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:
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.
Hi,
Share the download link of the Excel file. In that file, please show the expected result as well.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |