Lookup value with multiple criteria

[johnce123]

I have the following table called "SF" i would like to do a lookup to return "Annual Amount"

The other table i need to return the value into has "Site Fee Code" as a column (table below) I need to create a column in this table which returns the annual amount from SF table above.  

As you can see though in the first table there are two values of "CF Standard" for instance so i need it to return the value that relates to 2021 season. When we move into 2022 then i will need it to return 2022. So CURRENTYEAR & "SEASON" (2021SEASON) in this case, as in the table each year will be added a new Annual Amount. 

Trying to get a Sum amount for all the entities we have tagged to each different site fee code. 

Thanks

[parry2k]

@johnce123 you need to put the data in an excel sheet and explain it better, it is not easy to follow without looking at what you want to achieve?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[parry2k]

@johnce123 the 2nd table you added, does the site fee code is a unique value?

[johnce123]

There a Multiple of them in the second table.  Each line in this table represents a Person.  That person is assigned a Site fee code based on where they have our product. I need to return the value from the first table that relates to this site code, but i need it to return the value that relates to 2021SEASON and not 2020SEASON

[parry2k]

@johnce123 can you share the sample data of both the table and what would be the logic to get the value from the order table. Your post is not very clear.

[johnce123]

Hi, I have added the other table in, hopefully this makes sense now. 

Thanks