Get value from another table based on 2 conditions

[zbishhh2]

Hello I am new to PowerBI and don't even know where to begin with this formula.

I have 2 tables, one is called "cr791_hourlytracking", the other is a list of invoice numbers with account and week start date as columns like so called "Table1". I created a relationship between the two tables for the account and week start date columns.

I want to create a new column in the "cr791_hourlytracking" table that pulls invoice numbers based on account and week start date. I have an account and week of column in both tables. 

Table names in "cr791_hourlytracking" are 

"Week Start Date"

"Account"

So I need something like 

If cr791_hourlytracking[weekstartdate] = table[week of] 

and cr791_hourlytracking[account] = table1[account]

then return table1[invoicenumber]

[zbishhh2 · Accepted solution]

I tried this and it didn't work. Returned blanks. Not sure why

LOOKUPVALUE(Table1[Invoice Number],Table1[Week of],cr791_hourlytracking[Week Start Date],Table1[cr791_Account],cr791_hourlytracking[Account])

[AlexisOlson]

That seems like it should work unless there are multiple Invoice Number values for a given week and account.

Can you verify that both week columns are date type and the dates do align like you expect?

[zbishhh2]

Yeah that fixed it. I guess when I made the list of invoice numbers and dates I messed up. I cleaned it up and the formula worked.