How to capture corresponding manager ID based on transaction date if manager keeps changing

Hello friend_anand 

You may use the following DAX post creating relationship between the two tables:

ManagerID = 
CALCULATE (
    VALUES ( dtEmployee[MGR ID] ),
    FILTER (
        RELATEDTABLE ( dtEmployee ),
        dtTransaction[TRAN DATE] >= dtEmployee[START DATE]
            && dtTransaction[TRAN DATE]
                <= IF ( ISBLANK ( dtEmployee[END DATE] ), TODAY (), dtEmployee[END DATE] )
    )
)

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Hellofriend_anand 

I have validated the formula with the conditions you have mentioned:

For Manger 1, the date range is the same for Emp 1 & Emp 2

EMP 1

EMP 2

Have you created the relationship between these two tables?

In this case, it would be many-to-many with bi-directional filters

Regards,

Vivek