Getting the most recent and second most recent values by group (without dates)

[olimilo]

Hi everyone,

I'm trying to get the most recent and second most recent values in my table. I've looked up several examples on here, but the my data doesn't have a date value to check the most recent value with, although I can use a TestID to check the chronological order of the data with (eg: lower TestID means this was done earlier).

Any suggestion on how I can go about with this?

StudentID	TestCode	TestID	Rating
Student0061	Test00002	299470	92.54
Student0061	Test00002	318610	38.15
Student0061	Test00003	318610	54
Student0061	Test00006	318610	43.91
Student0062	Test00003	310912	89
Student0062	Test00003	324512	89
Student0063	Test00011	301876	46.63
Student0063	Test00011	311438	100
Student0064	Test00003	296873	96
Student0065	Test00005	311371	96.82
Student0066	Test00003	307311	75
Student0066	Test00003	308344	77
Student0066	Test00003	321068	74

StudentID	TestCode	Most Recent	2nd Most Recent
Student0061	Test00002	38.15	92.54
Student0061	Test00003	54
Student0061	Test00006	43.91
Student0062	Test00003	89	89
Student0063	Test00011	100	46.63
Student0064	Test00003	96
Student0065	Test00005	96.82
Student0066	Test00003	74	77

[parry2k]

@olimilo here are two measures which would work

Most Recent = 
CALCULATE( 
    MAX( Table2[Rating] ), 
    FILTER( 
        ALLEXCEPT( Table2, Table2[StudentID], Table2[TestCode] ), 
        Table2[Index] = MAX( Table2[Index] ) 
    ) 
) 

Most Recent 2 = 
CALCULATE( 
    MAX( Table2[Rating] ), 
    FILTER( 
        ALLEXCEPT( Table2, Table2[StudentID], Table2[TestCode] ), 
        Table2[Index]=  MAX( Table2[Index] ) - 1 
    )
) 

[olimilo]

Hi @parry2k 

Thank you, but unfortunately both measures didn't work for me. Also, using an Index - 1 filter for the 2nd most recent took data from other students and other test codes.

[parry2k]

@olimilo I think I missed to point out that you need to add index field using query editor. 

[parry2k · Accepted solution]

@olimilo solution attached, look at table2 and page 3 in attached.