Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Count Amount of Active Employees by period and adjust based on dynamic filters

  • 1.  Count Amount of Active Employees by period and adjust based on dynamic filters

    Posted Dec 20, 2019 11:04 AM

    I'd like to count the number of active employees in a certain period.
    It looks like I have a little bit of work to do because the calculated measure I currently use doesn't really work that well.

    I have a table with all employee assignments. Each employee can have multiple assignments and sometime those assignments might overlap.
    I have a start and end date for each of these assignments and I also have a Date Table that is linked to start date of the employee assignments table.

    1. How can I count the active number of employees and account for the fact that an employee might have multiple assignments? If I count distinct wouldn't that ignore the other assignments that the employee has?
    2. The formula I currently doesn't work correctly when I filter the table by activity type for example, it keeps the same ratio when applying filters which is obviously not correct. I'm new to Power BI, I'm assuming this is because I have to create a Measure ahead of time for each filter I might use and don't allow the user to change the filters?

    Here's the current formula:

    Count of Active Employees Measure =
    VAR endOfPeriod =MAX ( 'Employees-Date-Table'[Date] )

    VAR startOfPeriod = MIN( 'Employees-Date-Table'[Date] )
    DISTINCTCOUNT('EmployeesActivity'[Employee Name]),
    FILTER (
    ( 'EmployeesActivity'[Start date] <= endOfPeriod
    && 'EmployeesActivity'[End date] >= startOfPeriod)

    Here's a sample table:

    Employee Name Start Date End Date ActivityType
    Ben 12/20/2019 12/22/2019 Project
    Ben 12/21/2019 12/25/2019 Project
    Ben 12/22/2019 12/24/2019 Project
    Steven 12/21/2019 12/22/2019 Project
    Mike 12/21/2019 12/23/2019 Project
    Mike 12/27/2019 12/28/2019 Travel
    Tim 12/26/2019 12/26/2019 Travel
    Tim 12/14/2019 12/17/2019 Project

    Expected result for December: 4
    Expected result for next week (this week ends sunday the 22nd): 3 (Ben, Mike, Tim)
    Expected result for next week when filtering by Project - 2 (Ben, Mike)

  • 2.  RE: Count Amount of Active Employees by period and adjust based on dynamic filters

    Top Contributor
    Posted Dec 20, 2019 11:58 PM
    Hi !

    I think you have figured the most part correctly try switching from ALL('EmployeesActivity') to ALLEXCEPT('EmployeesActivity', 'ActivityType').


    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan