date range.
1 Topic
Date Range Lookup Across 2 Tables to Populate a Column with Associated Valuess
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.Solved953Views0likes3Comments