Forum Discussion

rdonohoe's avatar
rdonohoe
Regular Visitor
8 years ago
Solved

Active Employees per Period

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 

 

 

Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    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
        )
    )

     

1 Reply

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    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
        )
    )