Active Employees per Period

[rdonohoe]

Hi, 

I have a table with employees showing start and end dates. I have a chart showing the number of employees who left and joined in a particular month. 

I would like to add another count; the number of employees employeed on the last day of the period (in my case month) 

It seems to me I need to write a measure that checks that start date is less than or equal to the begining of the current period and the end date is empty or greater than the current period. 

I would appreciate help with this.

Rob 

[mattbrice · Accepted solution]

Assuming you have 'Year'/'Month' on rows/columns, perhaps something like this:

Employeed last day of Month =
VAR _lastDateOfPeriod =
    LASTDATE ( Calendar[Date] )
RETURN
    COUNTROWS (
        FILTER (
            EmployeeTable,
            VAR _hireDate = EmployeeTable[StartDate]
            VAR _termDate =
                IF ( ISBLANK ( EmployeeTable[EndDate] ), DATE ( 2099, 1, 1 ), EmployeeTable[EndDate] )
            RETURN
                _hireDate <= _lastDateOfPeriod
                && _termDate >= _lastDateOfPeriod
        )
    )