Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Headcount & turnover analysis

My numbers are not coming correct...headcount at the beginning of March should be the same as headcount at the end of February. But it's not the same.

I have used below formulas to solve the same

Headcount at the end of each month:

Active_Employees = CALCULATE(COUNTX(
    FILTER(
        'All Employee Data',
        'All Employee Data'[START DATE] <= MAX(Date_Table[Date])
        && (ISBLANK('All Employee Data'[END DATE]) || 'All Employee Data'[END DATE] > MAX('Date_Table'[Date]))
    ),
    'All Employee Data'[FIRST NAME]
), CROSSFILTER('Date_Table'[Date], 'All Employee Data'[START DATE], None))
 
Headcount_Beginning_Month = CALCULATE(
    COUNTX(
        FILTER(
            'All Employee Data',
            'All Employee Data'[START DATE] <= EOMONTH(MAX(Date_Table[Date]), -1) + 1
            && (ISBLANK('All Employee Data'[END DATE]) || 'All Employee Data'[END DATE] > EOMONTH(MAX(Date_Table[Date]), -1))
        ),
        'All Employee Data'[FIRST NAME]
    ),
    CROSSFILTER('Date_Table'[Date], 'All Employee Data'[START DATE], None)
)
 
Average_Headcount = ([Headcount_Beginning_Month]+ [Active_Employees])/2
 
Headcount Change = [Hired]/[Headcount_Beginning_Month]
 
Hired = CALCULATE(count('All Employee Data'[FIRST NAME]), USERELATIONSHIP('Date_Table'[Date], 'All Employee Data'[START DATE]))
 
Termed = CALCULATE(count('All Employee Data'[FIRST NAME]), USERELATIONSHIP('Date_Table'[Date], 'All Employee Data'[END DATE]), not ISBLANK('All Employee Data'[END DATE]))
 
Turnover = ('All Employee Data'[Termed]/'All Employee Data'[Headcount_Beginning_Month])
Need Help
Show and Tell
Tutorial Requests
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi Anonymous ,

     

    To create a flexible headcount analysis which respects time dimension you only need two tables,

    • An employee fact table
    • A calendar table

    For doing the correct headcount analysis, you need to set your calendar table as a disconnected table from your employee fact table.  Hence I believe that you do not need to resort to crossfilter function in your calculation.    

    You just need the 3 pieces of information in your employee fact table, which I believe you already have.

    • Employee ID
    • Starting Date
    • Leaving Date

    To enable slicing of headcount by time-dimension at any point in time, we have to keep the calendar table and employee fact table as disconnected tables as mentioned above.

    The DAX formula below provides a flexible headcount number at any historical point in time:

     

    Headcount =
SUMX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate]
&& EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

     

    There's an article which discusses how to prepare headcount analysis in the following link.  

    https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

    I hope it will be of use.  

    Best regards,

1 Reply

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous ,

     

    To create a flexible headcount analysis which respects time dimension you only need two tables,

    • An employee fact table
    • A calendar table

    For doing the correct headcount analysis, you need to set your calendar table as a disconnected table from your employee fact table.  Hence I believe that you do not need to resort to crossfilter function in your calculation.    

    You just need the 3 pieces of information in your employee fact table, which I believe you already have.

    • Employee ID
    • Starting Date
    • Leaving Date

    To enable slicing of headcount by time-dimension at any point in time, we have to keep the calendar table and employee fact table as disconnected tables as mentioned above.

    The DAX formula below provides a flexible headcount number at any historical point in time:

     

    Headcount =
SUMX (
EmployeeFact,
    IF (
EmployeeFact[Start] <= [SelectedDate]
&& EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

     

    There's an article which discusses how to prepare headcount analysis in the following link.  

    https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

    I hope it will be of use.  

    Best regards,