Forum Discussion

DarshanKumar's avatar
DarshanKumar
Icon for Helper II rankHelper II
1 year ago
Solved

Calculating Active Employees by specific Date

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 infor...
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    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)
    )
)
DarshanKumar's avatar
DarshanKumar
Icon for Helper II rankHelper II
1 year ago

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 IDHire DateJob Information: DateLocationDepartmentTerminaion Date
05301/08/202001/03/2019LuxembourgCorporate Services [CSVC]30/06/2022
00040102/02/202401/11/2024LondonRegulatory and Compliance 
00040102/02/202415/08/2024LondonRegulatory and Compliance 
00040102/02/202429/05/2024LondonRegulatory and Compliance 
00040102/02/202422/05/2023LondonRegulatory and Compliance 
15342803/02/202503/02/2025MauritiusGlobal Operations 
15251902/05/202301/01/2025MauritiusGlobal Operations03/03/2025
15251902/05/202307/08/2024MauritiusCorporate Services03/03/2025
15251902/05/202310/05/2024MauritiusCorporate Services03/03/2025
15251902/05/202301/03/2024MauritiusCorporate Services03/03/2025
15251902/05/202302/05/2023MauritiusCorporate Services03/03/2025
15244420/03/202301/03/2025MauritiusCorporate Services 
15244420/03/202301/01/2025MauritiusGlobal Operations 
15244420/03/202302/12/2024MauritiusCorporate Services 
15244420/03/202301/03/2024MauritiusCorporate Services 
15244420/03/202320/04/2023MauritiusCorporate Services 
15244420/03/202320/03/2023MauritiusCorporate Services 
20601801/06/200601/03/2023MauritiusPrivate Clients 
20601801/06/200616/01/2023MauritiusCorporate Services 
20601801/06/200610/01/2022MauritiusCorporate Services 
20601801/06/200601/09/2020MauritiusCorporate Services [CSVC] 
20601801/06/200601/06/2006MauritiusCorporate 
15258305/06/202320/02/2025MauritiusGlobal Operations 
15258305/06/202301/11/2024MauritiusGlobal Operations 
15258305/06/202301/03/2024MauritiusGlobal Operations 
15258305/06/202312/01/2024MauritiusGlobal Operations 
15258305/06/202329/11/2023MauritiusGlobal Operations 
15258305/06/202305/06/2023MauritiusGlobal Operations 
15331828/10/202420/11/2024MauritiusFinance 
15331828/10/202408/11/2024MauritiusFinance 
57714/05/201814/05/2018JerseyPrivate Clients20/10/2021
03601/07/201614/08/2024LuxembourgCorporate Services 
03601/07/201601/03/2024LuxembourgCorporate Services 
03601/07/201610/01/2022LuxembourgCorporate Services 
03601/07/201601/09/2021LuxembourgCorporate Services [CSVC] 
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

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