Starters and Leavers

[JKen1234]

I need to write a Dax measure to identify starters and leavers but have some complications in the data and cant work out how to write it.

Each person could have multiple "appointments"(Role) such as a bank and a full time postion and may end a role but not leave or transition from one appointment to another. I also need the measure to be able to filter out bank roles and still work if we just want to look at non bank employees. Due to the nature of the work we get quite a few people rejoin and get put in under the same person number

If the data was entered correctly people would have a starting date only when they first start and a leaving date when they have left at a person level. The start and end date would indicate when each role has ended. but this doesnt seem to be the case.

I think I need to write a dax measure along the lines of a distinct count of person number if they have an appointment leaving date in the period without another appointment that is active in the period. but I cannot work out if this is possibel or how to go about it.

Data example below

Person Number	Appointment Number	Start Date	Starting Date	End Date	Leaving Date	Appointment Type
123	1003	01/02/2001	(blank)	01/09/2006	01/09/2006	Permament
123	1187	02/01/2007	02/01/2007	02/09/2008	02/09/2008	Temporary
123	1250	03/09/2008	(blank)	31/10/2022	31/10/2022	Bank
123	1256	02/01/2007	02/01/2007	31/10/2022	31/10/2022	Bank
123	1527	01/10/2015	01/10/2015	30/06/2016	(blank)	Temporary
456	1613	23/03/2020	23/03/2020	31/10/2022	31/10/2022	Bank
789	1618	16/12/2020	16/12/2020	07/10/2022	07/10/2022	Permament
101	1630	08/01/2021	08/01/2021	20/10/2022	20/10/2022	Bank
101	1651	03/08/2022	03/08/2022	04/10/2022	(blank)	Temporary
121	1639	25/01/2022	25/01/2022	19/10/2022	19/10/2022	Permament
145	163	02/12/2021	02/12/2021	04/10/2022	04/10/2022	Bank
145	1646	04/07/2022	04/07/2022	04/10/2022	04/10/2022	Temporary
167	1643	19/04/2022	19/04/2022	26/10/2022	26/10/2022	Permament
189	1649	21/06/2022	21/06/2022	03/10/2022	03/10/2022	Permament
189	1653	04/10/2022	04/10/2022	(blank)	(blank)	Bank
201	1644	07/05/2022	07/05/2022	27/10/2022	27/10/2022	Bank
223	1652	13/09/2022	13/09/2022	13/10/2022	13/10/2022	Permament
245	1659	03/10/2022	03/10/2022	10/10/2022	10/10/2022	Permament

Any help would be greatly appreciated!

[amitchandak]

@JKen1234 , refer if approach in this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or in the attached files

[JKen1234]

Hi, Thanks unfortunately those solutions dont quite fit my data issues. Ive tried wrting something along the lines of the below but I cant work out how to get the not fucntion to impact the first part.

=CALCULATE(DISTINCTCOUNT(Appointments[Person Number]),FILTER(Appointments,
Appointments[Leaving Date]>=MIN('Calendar'[Date])&&Appointments[Leaving Date]<=MAX('Calendar'[Date])&&NOT(Appointments[Start Date]<=MAX('Calendar'[Date])&&([End Date]>MAX('Calendar'[Date])||ISBLANK([End Date])))
)
)