Display most recent record or highest value for each category

[gancw1]

I have a table of student marks with the date the tests are taken.

Student	Date	Marks
A	10/9/2022	50
A	11/9/2022	90
A	15/9/2022	80
B	11/9/2022	90
B	12/9/2022	70
C	10/9/2022	85
D	14/9/2022	50
D	16/9/2022	60

How do I create tables showing the first attempt marks, the last attempt marks and the highest marks for each student  ?

First attempt
Student	Date	Marks
A	10/9/2022	50
B	11/9/2022	90
C	10/9/2022	85
D	14/9/2022	50

Last attempt
Student	Date	Marks
A	15/9/2022	80
B	12/9/2022	70
C	10/9/2022	85
D	16/9/2022	60

Highest marks
Student	Date	Marks
A	11/9/2022	90
B	11/9/2022	90
C	10/9/2022	85
D	16/9/2022	60

[amitchandak · Accepted solution]

@gancw1 , refer my blogs can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[gancw1]

@amitchandak 

In your code

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[ID] = Max(Data[ID])), Data[Date]) 
return 
CALCULATE(max(Data[Status]), filter((Data) , Data[Date] =_max))

What does this expression do ?

Data[ID] = Max(Data[ID])

[gancw1]

@amitchandak,

Thanks.

I managed to display the date and marks for the first and last attemps.

First Date = MINX( 
                  FILTER(ALLSELECTED(Table_Marks), Table_Marks[Student]=MIN(Table_Marks[Student])), 
                  Table_Marks[Date] )

First Mark = 
    CALCULATE(
        max(Table_Marks[Marks]), FILTER(Table_Marks, Table_Marks[Date]= [First Date]))

However I am unable to get the date for the highest marks.

I have these 2 measures :

Highest Mark = max(Table_Marks[Marks])
Highest Marks Date = CALCULATE(max(Table_Marks[Date]), FILTER(Table_Marks, Table_Marks[Marks]= [Highest Mark]))

This will give an incorrect date when a lower mark is attained later

In addition, how do I calculate the Average of all the last attempt marks ?