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

[friend_anand]

I have one table showing transaction date and employee ID.  And another table showing employee ID, start date and end date for each of the manager ID that he has been assigned to so far.   I want to get the corresponding manager ID based on the transaction date in my first table.  Below are the tables:

Note: If end date is blank, then that manager ID applies until current date.

Table 1: Transaction Table		Table 2: Emp_Manager Table
TRAN DATE	EMP ID		EMP ID	MGR ID	START DATE	END DATE
3/25/2014	E00001		E00001	M00001	4/1/2011	3/31/2016
2/18/2015	E00001		E00001	M00002	4/1/2016	7/31/2019
10/12/2015	E00001		E00001	M00003	8/1/2019
4/8/2016	E00001		E00002	M00001	4/1/2011	3/31/2017
9/15/2016	E00001		E00002	M00002	4/1/2017	1/31/2020
2/16/2017	E00001		E00002	M00003	2/1/2020
11/20/2017	E00001		E00003	M00004	4/1/2011	12/31/2015
5/6/2018	E00001		E00003	M00005	1/1/2016
12/9/2018	E00001
3/9/2019	E00001
7/31/2019	E00001
8/1/2019	E00001
12/15/2019	E00001
2/20/2020	E00001
3/30/2014	E00002
2/23/2015	E00002
10/17/2015	E00002
4/13/2016	E00002
9/20/2016	E00002
2/21/2017	E00002
11/25/2017	E00002
5/11/2018	E00002
12/14/2018	E00002
3/14/2019	E00002
8/5/2019	E00002
8/6/2019	E00002
12/20/2019	E00002
2/25/2020	E00002
4/4/2014	E00003
2/28/2015	E00003
10/22/2015	E00003
4/18/2016	E00003
9/25/2016	E00003
2/26/2017	E00003
11/30/2017	E00003
5/16/2018	E00003
12/19/2018	E00003
3/19/2019	E00003
8/10/2019	E00003
8/11/2019	E00003
12/25/2019	E00003
3/1/2020	E00003

[vivran22 · Accepted solution]

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/

[friend_anand]

Hi Vivek,

I just notice that if the date range is same for 2 EMP ID's,  then it returns blank in the MGR ID.  

It should actually pick the corresponding MGR ID based on the EMP ID.  However, in the DAX you provided, I am not sure if it checks the EMP ID anywhere.  Please check and advise. 

Thanks, Anand

[vivran22 · Accepted solution]

Hello@friend_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

[friend_anand]

Yes, it works now.. I think there was an issue in the relationship.. I set it right now.. 

Thanks again, Vivek

[vivran22 · Accepted solution]

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/

[friend_anand]

Thank you, Vivek.

It works for me 🙂