Headcount & turnover analysis

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,