DAX help for Vlookup

[SonaSinghA]

HI Experts,

Can I create a vlookup formula using dax based on below conditons
I have a table with ID and Reporting date
And another table with same ID, Status. Raised date and Closure date .

I need to add Status in the first table baed on below two conditions

if Reporting date  >= Raised date  &&
   Reporting date  <= Closure date then I need to show staus.

Please help

[ryan_mayu]

pls provide some sample data

[SonaSinghA]

Project ID	Reporting date	Status
100	01-01-2023	Yes
100	04-01-2023
101	01-01-2023	No
101	04-01-2023
Project ID	Raised date	Closure date	Status
100	01-01-2023	15-01-2023	Yes
101	10-01-2022	15-01-2022	No
101	01-01-2023	03-01-2023	No

@ryan_mayu , please check

[Ashish_Mathur]

Hi,

In Table1, write this calculated column formula

Status = CALCULATE(MAX(Table2[Status]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Raised date]>=EARLIER(Table1[Reporting date])&&Table2[Raised date]<=EARLIER(Table1[Reporting date])))

Hope this helps.

[ryan_mayu · Accepted solution]

@SonaSinghA 

pls try this

Column = maxx(FILTER('Table (2)','Table (2)'[ID]='Table'[ID]&&'Table'[Reporting date]>='Table (2)'[Raised date]&&'Table'[Reporting date]<='Table (2)'[Closure date]),'Table (2)'[Status])

 the date of 100 does not between the raised date and closure date. So shall we display blank?

pls see the attachment below

[SonaSinghA]

Hi @ryan_mayu , we cant use one to many relationship here, same id's will repeate in the second table with different raised date. 

[ryan_mayu]

still not clear about this. 100's reporint date does not match 

Reporting date  >= Raised date  &&   Reporting date  <= Closure date 

so that's why it returns blank.

[SonaSinghA]

@ryan_mayu , Sorry, it was my mistake. Still, I can't use one-to-many relationships because Project IDs will repeat every month for new issues

.

[ryan_mayu]

we don't need to create the relationships between two tables

you can delete the relationship and have a try