Lookupvalue based on dates conditions

[anorville]

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

[ryan_mayu · Accepted solution]

@anorville

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])

[BA_Pete]

Ok, thanks @ryan_mayu .

I'll leave you to it.

Pete

[anorville]

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

Austin

[BA_Pete]

@anorville ,

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

[BA_Pete]

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

[anorville]

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

Austin