Forum Discussion
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
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])
6 Replies
Hi Anonymous ,
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
Hey! BA_Pete I am hoping to do this in DAX, unless you think Power Query would be best. Sorry for not specifying!
Austin
Anonymous ,
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
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])
