How to model data to show headcount by month

[Anonymous]

I am trying to set up an HR dashboard to show headcount by month. I have a Start Date and End Date for each employee, I also inserted a cond column showing if the employee is Active (1 if still with the company, 0 if he left), and a date table. I can't figure out what else I need to show that bar chart. I also believe that I need some date column (other than StartDate or End Date) to connect the main table to the date table.

Thanks for the help!

This is my sample data

[AlB · Accepted solution]

Hi @Anonymous

Try this:

1. Make sure your Date table includes all years in the data and every year in full. No relationship between 'Date' and your data table (Table1 in the code below)

2. Set 'Date'[Month] in the rows of a matrix visual (and 'Date'[Year] as well if required)

3. Create this measure and place it in values of the matrix: 

NumberActiveEmployees =
VAR _FirstDayCurrentMonth =
    MIN ( 'Date'[Date] )
VAR _LastDayCurrentMonth =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee #] ),
        FILTER (
            Table1,
            Table1[Hire Date] <= _LastDayCurrentMonth
                && (
                    Table1[End Date] >= _FirstDayCurrentMonth
                        || ISBLANK ( Table1[End Date] )
                )
        )
    )

This will count as active everyone that was employeed at least one day in that month.

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run some quick tests, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).