Forum Discussion
Post PartisanPromotions and Transfers
Hi,
I would like to know how I would be able to create a report based on employee promotions and transfers. I have the below dataset and would like to show the employees who have had a transfer within the business for the chosen month from a date table. E.g if i choose Apr 2020, then the report should show employee D as they have moved roles (using the valid from - valid to fields) and they are still with the company as the date_of_leaving is 31/12/9999.
TIA
| EMP_NO | NAME | POS_TITLE | VALID_FROM | VALID_TO | DATE_OF_EMPLOYMENT | DATE_OF_LEAVING |
| 10000 | Employee A | Database Manager | 01/10/1991 | 31/12/9999 | 01/10/1991 | 31/12/9999 |
| 10001 | Employee B | Gen Council & Company Secretary | 08/11/2004 | 31/12/9999 | 08/11/2004 | 31/12/9999 |
| 10002 | Employee C | Commercial Director | 02/10/2000 | 31/12/9999 | 02/10/2000 | 31/12/9999 |
| 10003 | Employee D | Business Improvement & Planning Manager | 04/09/1992 | 27/04/2020 | 04/09/1992 | 31/12/9999 |
| 10004 | Employee D | Head of Supply Chain | 28/04/2020 | 31/12/9999 | 04/09/1992 | 31/12/9999 |
| 10005 | Employee E | HR & Administration Manager | 01/08/2020 | 31/12/9999 | 04/05/1993 | 31/12/9999 |
| 10006 | Employee E | HR Business Partner | 04/05/1993 | 31/07/2020 | 04/05/1993 | 31/12/9999 |
| 10007 | Employee F | Data Manager | 20/09/1979 | 31/12/9999 | 20/09/1979 | 31/12/9999 |
| 10008 | Employee G | Data Team Administrator | 01/07/2005 | 10/01/2020 | 01/07/2005 | 10/01/2020 |
| 10009 | Employee H | Membership Administrator | 01/11/2020 | 31/12/9999 | 30/10/2006 | 31/12/9999 |
| 10010 | Employee H | Membership Contracts Manager | 30/10/2006 | 31/10/2020 | 30/10/2006 | 31/12/9999 |
| 10011 | Employee I | IT Services Manager | 25/09/2007 | 31/12/9999 | 25/09/2007 | 31/12/9999 |
| 10012 | Employee J | Membership Support Manager | 10/12/2007 | 31/08/2019 | 10/12/2007 | 31/12/9999 |
| 10013 | Employee J | Area Manager | 01/09/2019 | 31/12/9999 | 10/12/2007 | 31/12/9999 |
4 Replies
v-kelly-msftCommunity Support
Hi yaman123 ,
First create 2 columns as below:
Column_valid from = FORMAT('Table'[VALID_FROM],"MMM")&" "&YEAR('Table'[VALID_FROM])Column_valid to = FORMAT('Table'[VALID_TO],"MMM")&" "&YEAR('Table'[VALID_TO])Then create a new table as below:
Table 2 = UNION(VALUES('Table'[Column_valid from]),VALUES('Table'[Column_valid to]))And a measure as below:
_name = CALCULATE(MAX('Table'[NAME]),FILTER('Table',('Table'[Column_valid from]=SELECTEDVALUE('Table 2'[Column_valid from])||'Table'[Column_valid to]=SELECTEDVALUE('Table 2'[Column_valid from]))&&'Table'[DATE_OF_LEAVING]=DATE("9999","12","31")))And you will see:
For the related .pbix file,pls see here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
yaman123Hi,
Thanks for that. I have a date table which is used to select a month year to show data for. I would like the transfers table to show old job and new job columns if possible so its only one line per employee for the chosen month year? How would i be able to achieve that?
- v-kelly-msft
Hi yaman123 ,
Not quite clear,could you pls provide an expected output?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!