Forum Discussion

boot_heel's avatar
boot_heel
Frequent Visitor
7 years ago
Solved

Look-up values based on earliest date

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...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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's avatar
Ashish_Mathur
Super User
7 years ago

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.