Solved: Counting unique joiners and leavers within a month...

gfdytr4 · ‎07-27-2023

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

gfdytr4 · ‎08-10-2023

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

View solution in original post

gfdytr4 · ‎08-10-2023

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

gfdytr4 · ‎08-07-2023

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

ERD · ‎08-01-2023

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 ) )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

gfdytr4 · ‎08-02-2023

@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.

ERD · ‎08-02-2023

@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 ) )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

gfdytr4 · ‎08-01-2023

@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?

tamerj1 · ‎08-01-2023

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

gfdytr4 · ‎08-01-2023

@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.

tamerj1 · ‎08-02-2023

@gfdytr4
Please refer to updated sample file

tamerj1 · ‎07-27-2023

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 )
    )

gfdytr4 · ‎07-28-2023

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

tamerj1 · ‎07-28-2023

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.

gfdytr4 · ‎07-28-2023

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

tamerj1 · ‎07-28-2023

@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
                )
            )
        )
    )

tamerj1 · ‎07-28-2023

@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
                )
            )
        )
    )

gfdytr4 · ‎07-31-2023

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

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

Helpful resources

Join us at the Microsoft Fabric Community Conference

We want your feedback!

Microsoft Fabric Community Conference 2025

A Year in Review - December 2024

Join us at the 2025 Microsoft Fabric Community Conference