Forum Discussion

DarshanKumar's avatar
DarshanKumar
Helper 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 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)
    )
  • 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)
    )
)

5 Replies

  • DarshanKumar's avatar
    DarshanKumar
    Helper 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
      Super 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)
    )
)