Date Range Lookup Across 2 Tables to Populate a Column with Associated Valuess

[MattSwan]

Hi all,

I'm trying to replicate similar functionality to a VLOOKUP in Excel with range lookup set to TRUE.

I have two tables.

Collections has a date column.

Academic Years has a column called Academic Year and two date columns that specify a date range Start Date and End Date.

Table 1: Collections

Result Date
01/09/2020
05/05/2021
02/10/2021
02/02/2022

Table 2: Academic Year       

Academic Year    Start Date     End Date   
2020/2021        01/09/2020     31/08/2021
2021/2022        01/09/2021     31/08/2020

I would like to creat a column in the Collection table that looks up the Result Date in the Academic Year table and if it falls between the Start Date and End Date Display the Academic Year. Like this:

ResultDate       Academic Year
01/09/2020       2020/2021
05/05/2021       2020/2021
02/10/2021       2021/2022
02/02/2022       2021/2022

I've done some searching to see if I could find a solution. The code I have so far is below. However, this results in a blank column.

Academic Year = 
CALCULATE (
    SELECTEDVALUE ( 'Academic Years'[Academic Year] ),
    FILTER (
        'Academic Years',
        SELECTEDVALUE(Collections[Result Date]) >= 'Academic Years'[Start Date]
            && SELECTEDVALUE(Collections[Result Date]) <= 'Academic Years'[End Date] 
    )
)

Any assistance would be much appreciated.

[v-robertq-msft · Accepted solution]

Hi, @MattSwan 

According to your description, I can roughly understand your requirement, I think you can try to create a calculated column in the table ‘Collections’ like this to achieve your requirement

Academic Year =

CALCULATE(MAX('Academic Year'[Academic Year]),FILTER(ALL('Academic Year'),[Start Date]<=EARLIER(Collections[Result Date])&&[End Date]>EARLIER(Collections[Result Date])))

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[MattSwan]

Yes thank you! I can't pretend to know exactly what's going on, but it works!

[Greg_Deckler]

@MattSwan See if LOOKUPVALUE Range helps: (1) LOOKUPVALUE Range - Microsoft Power BI Community