topic Re: Date Range Lookup Across 2 Tables to Populate a Column with Associated Valuess in DAX Commands and Tips

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

MattSwan — Thu, 11 Nov 2021 10:14:00 GMT

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.

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

Greg_Deckler — Thu, 11 Nov 2021 11:01:42 GMT

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

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

v-robertq-msft — Mon, 15 Nov 2021 08:43:43 GMT

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.

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

MattSwan — Tue, 16 Nov 2021 10:37:40 GMT

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