cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
friend_anand
New Member

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

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 DATEEMP ID EMP IDMGR IDSTART DATEEND DATE
3/25/2014E00001 E00001M000014/1/20113/31/2016
2/18/2015E00001 E00001M000024/1/20167/31/2019
10/12/2015E00001 E00001M000038/1/2019 
4/8/2016E00001 E00002M000014/1/20113/31/2017
9/15/2016E00001 E00002M000024/1/20171/31/2020
2/16/2017E00001 E00002M000032/1/2020 
11/20/2017E00001 E00003M000044/1/201112/31/2015
5/6/2018E00001 E00003M000051/1/2016 
12/9/2018E00001     
3/9/2019E00001     
7/31/2019E00001     
8/1/2019E00001     
12/15/2019E00001     
2/20/2020E00001     
3/30/2014E00002     
2/23/2015E00002     
10/17/2015E00002     
4/13/2016E00002     
9/20/2016E00002     
2/21/2017E00002     
11/25/2017E00002     
5/11/2018E00002     
12/14/2018E00002     
3/14/2019E00002     
8/5/2019E00002     
8/6/2019E00002     
12/20/2019E00002     
2/25/2020E00002     
4/4/2014E00003     
2/28/2015E00003     
10/22/2015E00003     
4/18/2016E00003     
9/25/2016E00003     
2/26/2017E00003     
11/30/2017E00003     
5/16/2018E00003     
12/19/2018E00003     
3/19/2019E00003     
8/10/2019E00003     
8/11/2019E00003     
12/25/2019E00003     
3/1/2020E00003     
2 ACCEPTED SOLUTIONS

Accepted Solutions
vivran22 Memorable Member
Memorable Member

Re: 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/

View solution in original post

vivran22 Memorable Member
Memorable Member

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

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

Capture2.JPG

 

EMP 2

Capture1.JPG

 

Have you created the relationship between these two tables?

 

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

3.png

 

Regards,

Vivek

View solution in original post

5 REPLIES 5
vivran22 Memorable Member
Memorable Member

Re: 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/

View solution in original post

friend_anand
New Member

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

Thank you, Vivek.

It works for me 🙂

friend_anand
New Member

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

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 Memorable Member
Memorable Member

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

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

Capture2.JPG

 

EMP 2

Capture1.JPG

 

Have you created the relationship between these two tables?

 

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

3.png

 

Regards,

Vivek

View solution in original post

friend_anand
New Member

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

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

Thanks again, Vivek

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors