'Role' column to show persons role for a monthly report

[yaman123]

Hi all, 

I am looking to have a 'role' column created to show the role of the person in the company. Roles can change depending on the persons cost centre and site. From dataset below. emp 123456 should show as role 'Customer Services' as cost centre is 225 and site HO. Previous to this, emp was in role 'Finance' as cost centre was 101 and site UK. 

I will need the correct roles to show for a report which is run monthly. So if i select Apr 2024, 'Customer Services' should show as the role, but if I select Sept 2022, 'Finance' should show as the role for that month. 

Dataset below: 

EMP_NO	PROPERTY_CODE	PROPERTY_DESC	PROPERTY_VALUE	VALID_FROM	VALID_TO
123456	SITE	Site	UK	03/09/2019	30/09/2022
123456	COSTCTR	Cost Centre	250	03/09/2019	10/06/2020
123456	COSTCTR	Cost Centre	101	11/06/2020	30/09/2022
123456	SITE	Site	HO	01/10/2022	31/12/9999
123456	COSTCTR	Cost Centre	225	01/10/2022	31/12/9999

[ryan_mayu]

@yaman123 

pls check if this is what you want

pls see the attachment below

[yaman123]

Hi @ryan_mayu 

I am looking to have a new column created to show the 'Role' of the employee. So first screenshot would show a role column with 'Finance' and second screenshot would show 'Supply Chain' 

[ryan_mayu]

do you want to add a role filter? or just add the role info in the table? 

[yaman123]

add the role info as an additional column 

[ryan_mayu]

i think you must have another mapping table. could you pls provide that?

[yaman123]

Unfortunately there is not another mapping table which is probably not best practice. 

[ryan_mayu]

Then how can we know to display finance or customer services?  

maybe you can try something like below.

Measure = SWITCH([costcener],"225","Customer Services","101","Finance")

i can only display two department because i don't know the whole mapping relationship between costcenter and department

[yaman123]

the role is depending on the cost centre and site. I will need the report to pull through the correct role per month. If there has been no change in roles, then the latest role for the employee should show 

[ryan_mayu]

you need to provide the role mapping table, otherwise it's impossible to provide the solution.