I was hoping someone might be able to help with what I think should be quite simple but I can’t get to work.
I have a table with test results. Each test has 5 parts called the CAT4 type (overall, verbal, non-verbal, quantitative and spatial). Pupils sit the test in different years so we can see changes over time. This is shown in the test cycle column.
I’m trying to create a measure that calculates the average score based on only the most recent test a pupil has sat. The logic goes something like:
For each StudentID, filter the table to only show the max of test cycle, then average the CAT4 score.
Any help much appreciated.