Calculating Active Employees by specific Date

[DarshanKumar]

Hi,

Im trying to calculate the active employees by month. My employee table has duplicate values , multiple rows  of data for an employee. Each row has the same start date and end date and job information date. Job information date changes when ever the employee chnaged the department.

Below is the Dax formula i have used. But the active employee count measure  is not counting it corretcly  on those months when the employee has changed department. Any help is highly appreciated

ActiveEmployeesCount =

VAR thisdate = MAX(CalenderTable[Date])

RETURN

CALCULATE(

    DISTINCTCOUNT('Job Information'[Employee #]),

    FILTER(

        'Job Information',

        'Job Information'[Hire Date].[Date] <= thisdate &&

        (ISBLANK('Job Information'[Terminaion Date]) || 'Job Information'[Terminaion Date].[Date]>thisdate)

    )

[DarshanKumar]

@Sahir_Maharaj  Thank you very much it worked. Im a new bie in Dax and power BI. Thank you very much for your help.

[DarshanKumar]

@Sahir_Maharaj  thanks. I will check and come back to you.

Regards,

Darshan

[DarshanKumar]

@Greg_Deckler  Thansk for your promopt response. Please find the table. My requirement is to calculate active employees permonth. but I have duplicates in the employee table as some of the employees have changed department and I would need  active employees by department  for the selected month.

Emp ID	Hire Date	Job Information: Date	Location	Department	Terminaion Date
053	01/08/2020	01/03/2019	Luxembourg	Corporate Services [CSVC]	30/06/2022
000401	02/02/2024	01/11/2024	London	Regulatory and Compliance
000401	02/02/2024	15/08/2024	London	Regulatory and Compliance
000401	02/02/2024	29/05/2024	London	Regulatory and Compliance
000401	02/02/2024	22/05/2023	London	Regulatory and Compliance
153428	03/02/2025	03/02/2025	Mauritius	Global Operations
152519	02/05/2023	01/01/2025	Mauritius	Global Operations	03/03/2025
152519	02/05/2023	07/08/2024	Mauritius	Corporate Services	03/03/2025
152519	02/05/2023	10/05/2024	Mauritius	Corporate Services	03/03/2025
152519	02/05/2023	01/03/2024	Mauritius	Corporate Services	03/03/2025
152519	02/05/2023	02/05/2023	Mauritius	Corporate Services	03/03/2025
152444	20/03/2023	01/03/2025	Mauritius	Corporate Services
152444	20/03/2023	01/01/2025	Mauritius	Global Operations
152444	20/03/2023	02/12/2024	Mauritius	Corporate Services
152444	20/03/2023	01/03/2024	Mauritius	Corporate Services
152444	20/03/2023	20/04/2023	Mauritius	Corporate Services
152444	20/03/2023	20/03/2023	Mauritius	Corporate Services
206018	01/06/2006	01/03/2023	Mauritius	Private Clients
206018	01/06/2006	16/01/2023	Mauritius	Corporate Services
206018	01/06/2006	10/01/2022	Mauritius	Corporate Services
206018	01/06/2006	01/09/2020	Mauritius	Corporate Services [CSVC]
206018	01/06/2006	01/06/2006	Mauritius	Corporate
152583	05/06/2023	20/02/2025	Mauritius	Global Operations
152583	05/06/2023	01/11/2024	Mauritius	Global Operations
152583	05/06/2023	01/03/2024	Mauritius	Global Operations
152583	05/06/2023	12/01/2024	Mauritius	Global Operations
152583	05/06/2023	29/11/2023	Mauritius	Global Operations
152583	05/06/2023	05/06/2023	Mauritius	Global Operations
153318	28/10/2024	20/11/2024	Mauritius	Finance
153318	28/10/2024	08/11/2024	Mauritius	Finance
577	14/05/2018	14/05/2018	Jersey	Private Clients	20/10/2021
036	01/07/2016	14/08/2024	Luxembourg	Corporate Services
036	01/07/2016	01/03/2024	Luxembourg	Corporate Services
036	01/07/2016	10/01/2022	Luxembourg	Corporate Services
036	01/07/2016	01/09/2021	Luxembourg	Corporate Services [CSVC]

[Sahir_Maharaj · Accepted solution]

Hello @DarshanKumar,

Can you please try this approach:

ActiveEmployeesPerDepartment =
VAR SelectedMonth =
    MAX('Calendar'[Date])
RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Job Information'[Emp ID] ),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                'Job Information',
                'Job Information'[Emp ID],
                'Job Information'[Department],
                "LatestChange",
                    CALCULATE (
                        MAX ( 'Job Information'[Job Information: Date] ),
                        FILTER (
                            'Job Information',
                            'Job Information'[Emp ID] = EARLIER('Job Information'[Emp ID]) &&
                            'Job Information'[Department] = EARLIER('Job Information'[Department]) &&
                            'Job Information'[Job Information: Date] <= SelectedMonth
                        )
                    )
            ),
            "HireCheck", CALCULATE (
                MIN ( 'Job Information'[Hire Date] )
            ),
            "TermCheck", CALCULATE (
                MAX ( 'Job Information'[Terminaion Date] )
            )
        ),
        [HireCheck] <= SelectedMonth &&
        (ISBLANK([TermCheck]) || [TermCheck] > SelectedMonth)
    )
)

[Greg_Deckler]

@DarshanKumar Can you provide sample data as text?