Create new table based on status at most recent date

[Anonymous]

I have a very large dataset with multiple changes to student applications, and I would like to create a separate table which draws out only the most recent status of each student at a particular date, so that we have only 1 row of data per student rather then many.

The original table is laid out as follows:

StudentId	ChangeDate	CourseCode	Status	AcademicYear
Student1	01/10/2020	MA1	Applied	2021
Student1	08/10/2020	MA1	Offered	2021
Student1	08/10/2020	MA2	Accepted	2022
Student2	04/10/2020	EN1	Applied	2021
Student2	10/10/2020	EN1	Offered	2021
Student2	11/10/2020	EN1	Accepted	2021
Student3	02/10/2020	MA2	Applied	2021
Student3	03/10/2020	MA2	Withdrawn	2021
Student4	04/10/2020	EN1	Applied	2021
Student4	11/10/2020	EN2	Offered	2021
Student4	12/10/2020	EN2	Accepted	2021

The ChangeDate column notes the date of changes made to any of the other fields.  Students may have more than one change per date.

I would like the new table to record the most recent status for each student, so the result on the 8th October 2020 for the above would be:

Date	StudentId	CourseCode	Status	AcademicYear
08/10/2020	Student1	MA2	Accepted	2022
08/10/2020	Student2	EN1	Applied	2021
08/10/2020	Student3	MA2	Withdrawn	2021
08/10/2020	Student4	EN1	Applied	2021

and for 12th october would be:

Date	StudentId	CourseCode	Status	AcademicYear
12/10/2020	Student1	MA2	Accepted	2022
12/10/2020	Student2	EN1	Accepted	2021
12/10/2020	Student3	MA2	Withdrawn	2021
12/10/2020	Student4	EN2	Accepted	2021

Any suggestions greatly appreciated!  Thank you.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to get the student status at the most recent date based on the selection of Slicer. I think you can achieve this through a measure, you can try my steps:

1. Create a calendar table for the Slicer:

Date = CALENDARAUTO()

2. Create a measure:

StudentId1 =
var _selecteddate=
SELECTEDVALUE('Date'[Date])
var _rank=
RANKX(
    FILTER(ALLSELECTED('Table'),
    [StudentId]=MAX([StudentId])&&[ChangeDate]<=_selecteddate),
    CALCULATE(MAX('Table'[ChangeDate])+MAX('Table'[AcademicYear])),
    ,DESC,Dense)
var _filteredrank=
IF(MAX('Table'[ChangeDate])>_selecteddate,BLANK(),_rank)
var _studentid=
IF(_filteredrank=1,MAX('Table'[StudentId]),BLANK())
return _studentid

3. Create a Slicer and place ‘Date’[Date] then replace the [StudentId] in the original table chart with the new measure [StudentId1], like this:

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a new column of rank

rank = rankx(filter(Table, [student] = earlier([student])),[ChangeDate],,desc,dense)

Then create a new table to filter Rank =1

calculateTable(Table, filter(Table, [Rank]=1))

[Anonymous]

Thanks @amitchandak, I've successfully created the table based on the rank, which now shows us only the most recent status.

I would like this to be more dynamic if possible - I would like to be able to see the status of all students at a chosen date, as well as the final outcome, so in essence to move backwards in time and identify the exact status at a different point in the year.

Is this something which is possible using this method, or would I need a different approach?   I do have a "DayOfYearNumber" column on a linked Calendar table which aligns the date to a specific number, ie 01/10/2020 would be "DayOfYearNumber" = 1, as would 01/10/2019 etc, if that helps.