Forum Discussion
Helper VFind job position every month
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
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.
5 Replies
- Ashish_Mathur
Super User
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.
jlarquesHi 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?