Lookup by date

Hi Anonymous,

We can take the following steps to meet your requirement.

1.In power query, we need to remove the Name column and create a new table based on the unique Employye ID using the formula.

Table = DISTINCT(ALL(Table1))

2.Then create the relationship between the two tables.

3.Then I create a calculated column using the formula as below to get the result as we excepted.

Gruop2 = IF(DATEDIFF(DATE(2018,07,01),Table2[Evaluation date],DAY)>0 &&DATEDIFF(DATE(2018,07,15),Table2[Evaluation date],DAY)<0,RELATED('Table'[7/1/2018]),IF(DATEDIFF(DATE(2018,07,15),Table2[Evaluation date],DAY)>0 && DATEDIFF(DATE(2018,07,27),Table2[Evaluation date],DAY)<0,RELATED('Table'[7/15/2018]),IF(DATEDIFF(DATE(2018,07,27),Table2[Evaluation date],DAY)>0 &&DATEDIFF(DATE(2018,08,01),Table2[Evaluation date],DAY)<0,RELATED('Table'[7/27/2018]),RELATED('Table'[8/1/2018]))))

For more details, please check the pbix as attached.

https://www.dropbox.com/s/cvbou176d64tblw/Lookup%20by%20date3.pbix?dl=0

Regards,

Frank