New Measure to filter based on two parameters

[Camstr]

Hello,

I have a database that contains school test results by class and subject. 

The database is updated with the latest results and retains all previous records.

I want to make a Power BI report where I can visualise the latest average grade by class and subject based upon the most recent test date.

I need the report to update everytime new tests are taken, these will be future unknown dates.

See attached screenshot of what I have and what I want.

I know that I need a new measure here, but I am struggling to 'FILTER(ALLEXCEPT...' across two different tables.

Thanks in advance

[Camstr]

Thanks! @Dangar332  Worked perfectly.

[Dangar332 · Accepted solution]

hi, @Camstr 

with your current date format "dd-mm-yyyy"  use below code 

just adjust your table and column name

latest test date = 
    min(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )

out put like below

if date format like  "mm-dd-yyyy"  use below code 

just adjust your table and column name

latest test date = 
    max(parameter[test date])


updated grade = 
     MAXX(
        FILTER(parameter,
           parameter[test date]=[latest test date]
               ),
        parameter[average grade]
          )