Lookup Value with If

[cyborgandy]

Hi All,

I have two tables, I am writing a calculated column in table 2 to get the information that if Closed Date of table 2 is greater than equal to the month column of table 1 in that case in the new calculated column it should give as "No" otherwise lookup value from table 2 to get the status of the company code.let me know if anyone can help

Table 1:

Company Code	Company Name	Status	Closed Date
1	AV	Yes
2	VA	Yes	15/11/2023
3	RK	Yes
4	KR	Yes
5	AS	Yes	15/11/2023

 Table 2:

Company Code	Company Name	Supplier Nr	Supplier Name	Month	Calculated Coulmn
1	AV	111	ABC	Nov-23
2	VA	111	ABC	Nov-23
3	RK	111	ABC	Nov-23
4	KR	111	ABC	Nov-23
5	AS	111	ABC	Oct-23
1	AV	111	ABC	Oct-23
2	VA	111	ABC	Oct-23
3	RK	111	ABC	Oct-23
4	KR	111	ABC	Oct-23
5	AS	111	ABC	Dec-23
1	AV	111	ABC	Dec-23
2	VA	111	ABC	Dec-23
2	VA	111	ABC	Oct-23
2	VA	111	ABC	Dec-23
2	VA	111	ABC	Dec-23
3	RK	111	ABC	Dec-23
3	RK	111	ABC	Aug-23
3	RK	111	ABC	Aug-23
3	RK	111	ABC	Aug-23
5	AS	111	ABC	Aug-23
5	AS	111	ABC	Aug-23
5	AS	111	ABC	Dec-23
5	AS	111	ABC	Dec-23
5	AS	111	ABC	Aug-23
5	AS	111	ABC	Nov-23
2	VA	111	ABC	Sep-23
2	VA	111	ABC	Sep-23

[v-zhouwen-msft · Accepted solution]

Hi @cyborgandy ,

The table data is shown below:

Please follow these steps:

1. Use the following DAX expression to create a column

Column = IF(ISBLANK(LOOKUPVALUE(Table1[Closed Date],Table1[Company Code],'Table2'[Company Code])),
"YES",
IF(MONTH(LOOKUPVALUE('Table1'[Closed Date],'Table1'[Company Code],'Table2'[Company Code]))>MONTH('Table2'[Month]) ,
"NO",
"YES"))

2. Final output

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