Promotions and Transfers

[yaman123]

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

[v-kelly-msft]

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!

[yaman123]

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? 

[v-kelly-msft]

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!

[yaman123]

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