Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Betsy
Helper IV
Helper IV

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

 

Betsy_0-1614886693905.png

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:

 

Betsy_1-1614887263928.png

 

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

 

Betsy

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Betsy
Helper IV
Helper IV

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

 

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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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! 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.