Look-up values based on earliest date

[boot_heel]

This has got me scratching my head, and as much as I see many earliest date type solutions, I'm not able to apply any of them to my situation.

Here is the link to an image of my sample dataset on imgur (I can't figure out how to upload photos!?): https://imgur.com/rcTCrjA

I have two tables

1. Table of SKUs for item movements into inventory including the date the item is posted to inventory, the net amount of the item(s) with that SKU and the Site ID they were purchased for
2. Table of Site IDs that includes the multiple Plan Types and related Construction Start Dates

I can link the two tables by Site ID, however, I need to see SKUs by which Plan Type they are intended for. I want to select the plan type at the site where the construction start date is the next earliest date after the posting date of the SKU as I'm fairly confident this will give me a roughly accurate estimate. 

[v-xicai · Accepted solution]

Hi @boot_heel ,

You can create column Plan Type_matched to meet your demand.

Plan Type_matched= MAXX(TOPN(1,FILTER(Table2,Table2[Site ID]=Table1[Site ID]&& Table2[Cons.Start Date]>=Table1[Posting Date]),Table2[Cons.Start Date],DESC),Table2[Plan Type])

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

There need not be a relationship between the 2 Tables.  Write this calculated column formula in Table1

=LOOKUPVALUE(VALUES(Table2[Plan Type]),Table2[Cons. Start Date],CALCULATE(MIN(Table2[Cons. Start Date]),FILTER(Table2,Table2[Site ID]=EARLIER(Table1[Site ID])&&Table2[Cons. Start Date]>EARLIER(Table1[Posting Date]))),Table2[Site ID],Table1[Site ID])

If you face a problem with my formula, then share the link from where i can download your PBI file.