Find job position every month

[jlarques]

Dear community,

I have two tables; one is a job postions that develop every employee during a month, that it can be more than one change. The other one is a salary payroll, that depending of the job position and employee can has more than one salary payroll if its job position changes in the same month.

My intention is create a table with salary payroll date, employee name and job position. You can see that on the job position table, the empleado1 has same job position from february to april because its position does'nt change, so the table would be:

Do you have any idea to do this?

Thanks for your help.

José Luis 

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Paroll Salary table

Job position = CALCULATE(MAX('Job Position'[JOB POSlTlON]),FILTER('Job Position','Job Position'[EMPLOYEE]=EARLIER('Payroll Salary'[EMPLOYEE])&&'Job Position'[START DАТЕ]<=EARLIER('Payroll Salary'[PAYROLL DATE])&&'Job Position'[END DАТЕ]>=EARLIER('Payroll Salary'[PAYROLL DATE])))

Hope this helps.

[jlarques]

Hi @Ashish_Mathur ,

thanks for your quick answer.

I'm thinkg about how to implement your code, because job position and payroll salary are fact tables and I have a many to many relationship where job position filters payroll salary, and with FILTER function I cannot access to employee field from the payroll salary table where you comparte employee with employee from both tables.

Do you know any formula to access payroll salary with filter in job description with this relationship? RELATED is not working.

Thanks @Ashish_Mathur 

[Ashish_Mathur]

My formula has absolutely nothing to do with a relationship between the 2 tables.  Have you even tried it?  What result do you get?  What problem are you facing?

[jlarques]

Hi @Ashish_Mathur ,

you're right. I was wrong triying to do it in a different way but creating the column like you write it on a payroll table, I have a column with job position. 

Your solution is a right solution. 

Thanks for your help, @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.