Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@JKen1234 , refer if approach in this blog can help
or in the attached files
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])))
)
)
User | Count |
---|---|
49 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
100 | |
55 | |
28 | |
18 | |
13 |