Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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,
Kelly
Did I answer your question? Mark my post as a solution!
Hi,
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?
Hi @yaman123 ,
Not quite clear,could you pls provide an expected output?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi, I would like to show the below when April 2020 is selected from the date table
Name Old Position New Position Valid From
Employee D Business Improvement and Planning Manager Head of Supply Chain 28/04/2020
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |