How do I match a review file to its correct reporting month based on data from 2 tables?

[EllieSU]

Hi all, I need help figuring out the best way to match a completed report to its correct reporting month.

I have tried using lookupvalue. The issue I have is that I cannot match Reporting Month to Review Completed as some reviews can be completed in later months. What would be the best approach to this? 

For both tables, there is no unique id as some of them can be repeated.

For example there a list of review to be complete by employee with reporting month (Table 1).

Reporting Month	Review File Name	Employee Assigend
November 2023	ABC	Employee A
November 2023	DEF	Employee B
November 2023	GHI	Employee C
November 2024	ABC	Employee A
November 2024	DEF	Employee D
November 2024	JKL	Employee C

 Then we have another table (Table 2) that only shows the date the review is completed, review file name, and employee assigned.

Review Completed	Review File Name	Employee Assigned
15 December 2023	ABC	Employee A
10 November 2023	DEF	Employee B
5 Jan 2024	GHI	Employee C
23 November 2024	ABC	Employee A
10 December 2024	DEF	Employee D
12 November 2024	JKL	Employee C

The deired output is having the Reporting Month from Table 1 added to Table 2:

Review Completed	Review File Name	Employee Assigned	Reporting Month
15 December 2023	ABC	Employee A	November 2023
10 November 2023	DEF	Employee B	November 2023
5 Jan 2024	GHI	Employee C	November 2023
23 November 2024	ABC	Employee A	November 2024
10 December 2024	DEF	Employee D	November 2024
12 November 2024	JKL	Employee C	November 2024

[ryan_mayu · Accepted solution]

@EllieSU 

you can create a new column in table 1 and change it to date

then use DAX to create a column

Column =

 VAR _date=maxx(FILTER('Table 1','Table 1'[Reporting Month - Copy]<='Table  2'[Review Completed]),'Table 1'[Reporting Month - Copy])

 return maxx(FILTER('Table 1','Table 1'[Reporting Month - Copy]=_date),'Table 1'[Reporting Month])

pls see the attachment below

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Reporting month = FORMAT(CALCULATE(MAX(Table1[Reporting Month]),FILTER(Table1,Table1[Employee Assigend]=EARLIER(Table2[Employee Assigned])&&Table1[Review File Name]=EARLIER(Table2[Review File Name])&&Table1[Reporting Month]<=EARLIER(Table2[Review Completed]))),"mmmm-yyyy")

Hope this helps.

[ryan_mayu · Accepted solution]

@EllieSU 

you can create a new column in table 1 and change it to date

then use DAX to create a column

Column =

 VAR _date=maxx(FILTER('Table 1','Table 1'[Reporting Month - Copy]<='Table  2'[Review Completed]),'Table 1'[Reporting Month - Copy])

 return maxx(FILTER('Table 1','Table 1'[Reporting Month - Copy]=_date),'Table 1'[Reporting Month])

pls see the attachment below

[EllieSU]

Hi @ryan_mayu, If you don't mind, can you explain the logic behind that as well? Thanks so much

[ryan_mayu]

@EllieSU 

first to create a date column, only date can compare with data. Then we find the max date before the review complete data, the report month related to that max date is what we want to get.