Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anorville
Helper I
Helper I

Lookupvalue based on dates conditions

Good morning!

 

I have two tables:

1) 

LeaseNumEffectiveDatePriceContractNumberPrice
43644410/1/2020ENL-P072010520
4364447/1/2020ENL-P07201052-3.80

 

2)

LeaseNumTicketDateLast Contract Date LookupLast Price Lookup
4364447/1/20207/1/2020-3.80
4364448/1/20207/1/2020-3.80
4364449/1/20207/1/2020-3.80
43644410/1/202010/1/20200

 

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
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ok, thanks @ryan_mayu .

I'll leave you to it.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Austin

BA_Pete
Super User
Super User

@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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

Austin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.