Power BI Exchange

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:

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)

Hi !

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

Regards,

------------------------------
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
------------------------------

Original Message:
Sent: Dec 20, 2019 11:04 AM
From: Heath Heath
Subject: Count Amount of Active Employees by period and adjust based on dynamic filters

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

RETURN

CALCULATE (

DISTINCTCOUNT('EmployeesActivity'[Employee Name]),

FILTER (

ALL('EmployeesActivity'),

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