cancel
Showing results for
Did you mean:
Frequent Visitor

## Lookupvalue based on dates conditions

Good morning!

I have two tables:

1)

 LeaseNum EffectiveDate PriceContractNumber Price 436444 10/1/2020 ENL-P07201052 0 436444 7/1/2020 ENL-P07201052 -3.80

2)

 LeaseNum TicketDate Last Contract Date Lookup Last Price Lookup 436444 7/1/2020 7/1/2020 -3.80 436444 8/1/2020 7/1/2020 -3.80 436444 9/1/2020 7/1/2020 -3.80 436444 10/1/2020 10/1/2020 0

I am trying to pull in data from table 1 into table 2.

• Criteria 1 = if leasenum (table2) = leasenum (table1)
• Criteria 2 = if ticketdate (table2) is = ?

I am not sure the best most efficent way to calculate this to get the results desired ( Red Columns).

I really appreciate any help, thanks all!

"Hope to be a datanaut"

Austin

1 ACCEPTED SOLUTION
Super User II

maybe you can try to create two columns

``````Column = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]<=Table2[TicketDate]),Table1[EffectiveDate])

Column 2 = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]=Table2[Column]),'Table1'[Price])``````

Proud to be a Super User!

6 REPLIES 6
Super User II

maybe you can try to create two columns

``````Column = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]<=Table2[TicketDate]),Table1[EffectiveDate])

Column 2 = MAXX(FILTER(Table1,Table1[LeaseNum]=Table2[LeaseNum]&&'Table1'[EffectiveDate]=Table2[Column]),'Table1'[Price])``````

Proud to be a Super User!

Super User II

Ok, thanks @ryan_mayu .

I'll leave you to it.

Pete

Frequent Visitor

Amazing @ryan_mayu and @BA_Pete thanks for the quick replies! It works like a charm!

Austin

Super User II

I think this depends on how the DAX performs.

I'll have look now and see if I can put together something performant in DAX for you to test. If not, we'll do it in PQ.

Pete

Super User II

Hi @anorville ,

lol @ "Hope to be a Datanaut"!

*Edit* I didn't mean this in a mean way, I just thought it was a funny take on the "Proud to be a Datanaut" signatures 🙂

Are you aiming to do his in Power Query or DAX?

Pete

Frequent Visitor

Hey! @BA_Pete  I am hoping to do this in DAX, unless you think Power Query would be best.  Sorry for not specifying!

Austin

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!