topic Re: Counting unique joiners and leavers within a month excluding a specific division in DAX Commands and Tips

Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Thu, 27 Jul 2023 13:53:08 GMT

I am trying to calculate unique people joining or leaving per month. Except I want to exclude counting people leaving and joining the same division within the same month. I have an inactive connection between my StartDate and EndDate to my dimCalendar.

My employee data looks like the table below. EmployeeNr 1 should not be counted as either leaving or joining in May 2022 since he joining a project within the same division. Only divsion Sales has this creiterea. There are no blank values in my columns. I can shape the data in power query or SQL if there are any suggestions on how to do it there.

Row	EmployeeNr	StartDate	EndDate	Division	Project
1	1	01.01.2022	15.05.2022	Sales	SalesA
2	2	01.01.2022	09.10.2023	HR	HRA
3	3	04.03.2023	20.02.2025	Accounting	Acc1
4	4	07.03.2023	09.07.2024	Technical	TechA
5	2	25.07.2023	20.01.2025	Sales	SalesA
6	1	16.05.2022	29.11.2023	Sales	SalesB
7	3	28.02.2025	15.03.2026	Accounting	Acc2

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Thu, 27 Jul 2023 17:08:27 GMT

Hi gfdytr4
Please refer to attached sample file with the proposed solution

Joiners = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MinDate = MIN ( 'Date'[Date] ) RETURN SUMX ( VALUES ( Employee[EmployeeNr] ), VAR StartDate = CALCULATE ( MIN ( Employee[StartDate] ) ) RETURN IF ( StartDate >= MinDate && StartDate <= MaxDate, 1 ) --or INT ( StartDate >= MinDate && StartDate <= MaxDate ) )

Leavers = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MinDate = MIN ( 'Date'[Date] ) RETURN SUMX ( VALUES ( Employee[EmployeeNr] ), VAR EndDate = CALCULATE ( MIN ( Employee[EndDate] ) ) RETURN IF ( EndDate >= MinDate && EndDate <= MaxDate, 1 ) --or INT ( StartDate >= MinDate && StartDate <= MaxDate ) )

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Fri, 28 Jul 2023 08:25:23 GMT

Thank you for your reply tamerj1

I dont think your solution accounts for excluding the same employee in Sales that is changing projects within the same division in the same month.

In the sample table employee 1 is leaving SalesA within Sales and joining SalesB which is also within division Sales, in the same month. Employee 1 should therefore not be counted as either leaving or joining. This only needs to apply for people within the division Sales.

So the dax needs to include something like:
If EmployeeNr Leaves Sales division && New division = Sales && StartDate is same month as LeaveDate then exclude from counting him as either leaving or joining

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Fri, 28 Jul 2023 08:45:09 GMT

Hi gfdytr4

There is is a small error in the code (due to copy/paste effect :-D) but it is not as you think.
I think it was clearly desribed in your original post that employee 1 need not to be counted in May neither as Joiner nor as a leaver.
Emplyee 1 is counted as a joiner only once (in Jan 2020) which is correct but also need to be counted as a leaver only once (in NOV 2023) which is not the obtained result. The reason is that I missed to change MIN to MAX in the variable EndDate (line 7). Do that and it should work. Let me know if it doesn't. I should updae the solution as soon as I reach office.

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Fri, 28 Jul 2023 09:51:28 GMT

Thanks again for your promt reply tamerj1 .

There will still be an issue as I need a way to filter for only employees within Sales division to not be counted when leaving and joining Sales division within same month. If someone from HR or Accounting leaves and joins a project within the same division in the same month it should count as 1 leaving and 1 joining.

So if employeenr 1 was was working in Accounting he will be counted as leaving and joining in May 2022

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Fri, 28 Jul 2023 13:32:12 GMT

gfdytr4
Please refer to attached updated file

Joiners = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MinDate = MIN ( 'Date'[Date] ) RETURN SUMX ( VALUES ( Employee[Division] ), IF ( Employee[Division] = "Sales", SUMX ( CALCULATETABLE ( VALUES ( Employee[EmployeeNr] ) ), VAR StartDate = CALCULATE ( MIN ( Employee[StartDate] ) ) RETURN IF ( StartDate >= MinDate && StartDate <= MaxDate, 1 ) ), COUNTROWS ( FILTER ( CALCULATETABLE ( Employee ), Employee[StartDate] >= MinDate && Employee[StartDate] <= MaxDate ) ) ) )

Leavers = VAR MaxDate = MAX ( 'Date'[Date] ) VAR MinDate = MIN ( 'Date'[Date] ) RETURN SUMX ( VALUES ( Employee[Division] ), IF ( Employee[Division] = "Sales", SUMX ( CALCULATETABLE ( VALUES ( Employee[EmployeeNr] ) ), VAR EndDate = CALCULATE ( MAX ( Employee[EndDate] ) ) RETURN IF ( EndDate >= MinDate && EndDate <= MaxDate, 1 ) ), COUNTROWS ( FILTER ( CALCULATETABLE ( Employee ), Employee[EndDate] >= MinDate && Employee[EndDate] <= MaxDate ) ) ) )

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Fri, 28 Jul 2023 13:32:15 GMT

gfdytr4
Please refer to attached updated file

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Mon, 31 Jul 2023 10:27:49 GMT

Thanks again for you help tamerj1 !

It looks like its close but not quite there:

When I use the data in the picture there should be 3 leavers in Oct-2024 and 3 joiners in Jan-2025 since even though EmployeeNr1 is in sales, he is leaving Sales in Oct-2024 but not joining Sales again until Jan-2025.
Data:

RowEmployeeNrStartDateEndDateDivisionProject

1	1	01.01.2022	15.05.2022	Sales	A
2	2	01.01.2022	15.05.2022	Accounting	B
3	3	01.01.2022	15.05.2022	Tech	C
4	1	16.05.2022	10.10.2023	Sales	AA
5	2	16.05.2022	10.10.2023	Accounting	BB
6	3	16.05.2022	10.10.2023	Tech	CC
7	1	11.10.2023	15.10.2024	Sales	A
8	2	11.10.2023	15.10.2024	Accounting	B
9	3	11.10.2023	15.10.2024	Tech	C
10	1	20.01.2025	20.12.2025	Sales	A
11	2	20.01.2025	20.12.2025	Accounting	BB
12	3	20.01.2025	20.12.2025	Tech	CC

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Tue, 01 Aug 2023 10:59:24 GMT

tamerj1 I have been playing around with adding more "Employees" and the issue seems like now the Dax code is just looking at the first StartDate and last EndDate for division Sales, but if that employee joins or leaves Sales within that period they wont be counted.

Maybe obvious to you 🙂 Do you know if Dax is at all able to solve this case?

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Tue, 01 Aug 2023 17:58:12 GMT

gfdytr4
Yes you are right. But what be considered as a legitimate gap?

Re: Counting unique joiners and leavers within a month excluding a specific division

ERD — Tue, 01 Aug 2023 18:12:07 GMT

Hi gfdytr4 ,

You can try these measures:

joined = VAR min_dt = MIN ( 'Date'[Date] ) VAR max_dt = MAX ( 'Date'[Date] ) VAR t = FILTER ( ADDCOLUMNS ( 'Table', "check", VAR current_user = CALCULATE ( SELECTEDVALUE ( 'Table'[EmployeeNr] ) ) VAR current_st_dt = CALCULATE ( SELECTEDVALUE ( 'Table'[StartDate] ) ) VAR amt = COUNTROWS ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ) ) VAR min_dt_of_user = MINX ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ), 'Table'[StartDate] ) RETURN IF ( amt > 1, min_dt_of_user, current_st_dt ) ), [StartDate] = [check] ) RETURN COUNTROWS ( FILTER ( t, [StartDate] >= min_dt && [StartDate] <= max_dt ) )

left = VAR min_dt = MIN ( 'Date'[Date] ) VAR max_dt = MAX ( 'Date'[Date] ) VAR t = FILTER ( ADDCOLUMNS ( 'Table', "check", VAR current_user = CALCULATE ( SELECTEDVALUE ( 'Table'[EmployeeNr] ) ) VAR current_en_dt = CALCULATE ( SELECTEDVALUE ( 'Table'[EndDate] ) ) VAR amt = COUNTROWS ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ) ) VAR max_dt_of_user = MAXX ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ), 'Table'[EndDate] ) RETURN IF ( amt > 1, max_dt_of_user, current_en_dt ) ), [EndDate] = [check] ) RETURN COUNTROWS ( FILTER ( t, [EndDate] >= min_dt && [EndDate] <= max_dt ) )

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Wed, 02 Aug 2023 06:38:57 GMT

tamerj1

The gap is if they are not joining within the same month or longer is what I believe is needed. So if the same Sales employee leaves Sales 31-October-2022 and rejoins Sales 1-November-2023 it needs to be counted as leaving in October and then a joiner in November.

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Wed, 02 Aug 2023 07:44:44 GMT

ERD thanks for your input!

Unfortunately it is not exactly how i need it. My dummy-data was not optimal to show what i needed.

I have added 1 more row where Employeenr1 joines Sales in Jan-24. This means that there should be 1 leaver in Nov-23 (Employee1) since he does not join the new Sales project within the same month. Also employee1 should be counted as a joiner Jan-2024.

Re: Counting unique joiners and leavers within a month excluding a specific division

ERD — Wed, 02 Aug 2023 10:45:12 GMT

gfdytr4 , than it's 1 more requirement: same division (Sales) and same month.

I've changed measures:

joined = VAR min_dt = MIN ( 'Date'[Date] ) VAR max_dt = MAX ( 'Date'[Date] ) VAR t = FILTER ( ADDCOLUMNS ( 'Table', "check", VAR current_user = CALCULATE ( SELECTEDVALUE ( 'Table'[EmployeeNr] ) ) VAR current_st_dt = CALCULATE ( SELECTEDVALUE ( 'Table'[StartDate] ) ) VAR amt = COUNTROWS ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ) ) VAR prev_en_dt = MAXX ( FILTER ( 'Table', 'Table'[EndDate] < current_st_dt && 'Table'[Division] = "Sales" && 'Table'[EmployeeNr] = current_user && amt > 1 ), 'Table'[EndDate] ) VAR same_mon_yr = MONTH ( prev_en_dt ) = MONTH ( current_st_dt ) && YEAR ( prev_en_dt ) = YEAR ( current_st_dt ) RETURN IF ( NOT same_mon_yr, current_st_dt ) ), [StartDate] = [check] ) RETURN COUNTROWS ( FILTER ( t, [StartDate] >= min_dt && [StartDate] <= max_dt ) )

left = VAR min_dt = MIN ( 'Date'[Date] ) VAR max_dt = MAX ( 'Date'[Date] ) VAR t = FILTER ( ADDCOLUMNS ( 'Table', "check", VAR current_user = CALCULATE ( SELECTEDVALUE ( 'Table'[EmployeeNr] ) ) VAR current_st_dt = CALCULATE ( SELECTEDVALUE ( 'Table'[StartDate] ) ) VAR current_en_dt = CALCULATE ( SELECTEDVALUE ( 'Table'[EndDate] ) ) VAR amt = COUNTROWS ( FILTER ( 'Table', 'Table'[EmployeeNr] = current_user && 'Table'[Division] = "Sales" ) ) VAR next_st_dt = MINX ( FILTER ( 'Table', 'Table'[StartDate] > current_st_dt && 'Table'[Division] = "Sales" && 'Table'[EmployeeNr] = current_user && amt > 1 ), 'Table'[StartDate] ) VAR same_mon_yr = MONTH ( next_st_dt ) = MONTH ( current_en_dt ) && YEAR ( next_st_dt ) = YEAR ( current_en_dt ) RETURN IF ( NOT same_mon_yr, current_en_dt ) ), [EndDate] = [check] ) RETURN COUNTROWS ( FILTER ( t, [EndDate] >= min_dt && [EndDate] <= max_dt ) )

Re: Counting unique joiners and leavers within a month excluding a specific division

tamerj1 — Wed, 02 Aug 2023 16:22:44 GMT

gfdytr4
Please refer to updated sample file

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Mon, 07 Aug 2023 10:42:35 GMT

Thanks for your suggestions tamerj1 and ERD !

I have been testing your solutions. The sample data i have provided has not been optimal. In the table below is an example of a real employees schedule. According to the measures as they are now the results should be 1 joiner in 2018 and 1 leaver in 2018. Then only 1 joiner in 2020 as the employee is continously a part of atleast one project within Sales until the latest Enddate which is June 2024. I have attached a picture of the results from your measures.

Would it be possible to adjust the measures so that the time needed to count as a Joiner and Leaver is 30 days. So with the data in the table below the results should be 1 joiner in Feb-2018 and 1 Leaver at the latest date which is June-2024.

Row EmployeeNr Division Project StartDate EndDate

1	1	Sales	A	12.02.2018	31.12.2018
2	1	Sales	A	01.01.2019	30.04.2019
3	1	Sales	A	01.05.2019	15.12.2019
4	1	Sales	B	03.05.2019	31.01.2020
5	1	Sales	A	16.12.2019	30.11.2021
6	1	Sales	A	03.02.2020	15.06.2020
7	1	Sales	C	15.11.2021	15.05.2022
8	1	Sales	A	01.12.2021	02.03.2022
9	1	Sales	D	03.01.2022	31.08.2022
10	1	Sales	A	03.03.2022	30.04.2022
11	1	Sales	A	01.05.2022	31.12.2022
12	1	Sales	C	16.05.2022	30.03.2024
13	1	Sales	D	30.11.2022	12.02.2023
14	1	Sales	E	02.01.2023	01.07.2023
15	1	Sales	D	13.02.2023	30.06.2024
16	1	Sales	F	24.03.2023	31.08.2023
17	1	Sales	G	03.05.2023	02.07.2023
18	1	Sales	G	03.07.2023	01.01.2024

Re: Counting unique joiners and leavers within a month excluding a specific division

gfdytr4 — Thu, 10 Aug 2023 12:50:58 GMT

Manged to solve it in SQL by using LEAD and LAG functions.