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 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. 

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.

     

2 Replies

  • 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.

  • v-xicai's avatar
    v-xicai
    Community Support
    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.