Lookup with filter

[AllanBerces]

Hi good day,

Can anyone help me on my lookup value. I have two table and i want to look up the value from table 2 but filter by year. Pls refer below.

RESULT

Thank you

[Demert · Accepted solution]

Hi @AllanBerces ,

What I suggest is creating a new column in table01 and using calculate instead of lookup value. See below:

Progress2023 = CALCULATE(MAX(Table2[Daily Progress]), Table2[Year]=2023 , Table2[Work]=Table2[Work])

For every year we are creating a new column to calculate the max value to the corresponding work column and year 

[AllanBerces]

Hi @Demert thank you very much, its working