topic Re: Calculating Active Employees by specific Date in DAX Commands and Tips

Calculating Active Employees by specific Date

DarshanKumar — Fri, 04 Apr 2025 15:52:01 GMT

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)

)

Re: Calculating Active Employees by specific Date

Greg_Deckler — Fri, 04 Apr 2025 16:12:27 GMT

DarshanKumar Can you provide sample data as text?

Re: Calculating Active Employees by specific Date

DarshanKumar — Fri, 04 Apr 2025 17:05:55 GMT

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]

Re: Calculating Active Employees by specific Date

Sahir_Maharaj — Sun, 06 Apr 2025 16:55:48 GMT

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

Re: Calculating Active Employees by specific Date

DarshanKumar — Sun, 06 Apr 2025 19:54:37 GMT

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

Regards,

Darshan

Re: Calculating Active Employees by specific Date

DarshanKumar — Mon, 07 Apr 2025 11:30:31 GMT

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