cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WorkHard
Helper V
Helper V

Count Amount of Active Employees by period

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 NameStart DateEnd DateActivityType
Ben12/20/201912/22/2019Project
Ben12/21/201912/25/2019Project
Ben12/22/201912/24/2019Project
Steven12/21/201912/22/2019Project
Mike12/21/201912/23/2019Project
Mike12/27/201912/28/2019Travel
Tim12/26/201912/26/2019Travel
Tim12/14/201912/17/2019Project


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)

1 ACCEPTED SOLUTION
TomMartens
Super User II
Super User II

Hey @WorkHard ,

 

here you will find a solutions that provides a solution to your challenge:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EZeJfxufOaBJmhxXRyd9cUwBe0iRe...

The challenge you are facing has a name: event-in-progress.

There are a lot of blog posts about this problem available. Please be aware that my solution follows a different approach, as I expand the table that contains the start and end date in a way that the new table also has a single date column, in my solution I use this DAX statement to expand the base table:

EmployeesActivityDays = 
GENERATE(
    'EmployeesActivity'
    , var _DateStart = 'EmployeesActivity'[Start Date]
    var _DateEnd = 'EmployeesActivity'[End Date]
    return
    GENERATESERIES(_DateStart , _DateEnd , 1)
)

Please be aware that I added a column that allows to differentiate the activities.

The above approach allows to leverage the power of filter propagation by relating the calendar table to the date column of the new table.

 

There is a very simple measure:

No Of Active Employees = 
DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])

 

This allows to create two table visuals like so:

image.png

Don't be afraid of expanding the table and creating a table with hundreds of thousands to tenth of millions of rows, basically scanning a simple table always  performs much better than evaluating "compex" formulas, where different columns are combined forcing query materialization.

 

Hopefully this provides what you are looking for

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
Bertverderrie
Frequent Visitor

Hey @TomMartens 

I came across your awesome solution to this problem, it works perfectly in my model, thanks! I have one follow-up question though.

Your measure counts the amount of employees that were active at any date in the period in the visual. But in my model, I need to know which (in my case, projects) were active at the end of the period.

Any tips on how to work that into the measure?

Hey @Bertverderrie , 

 

this measure counts the active ... at the end of the period (the last date in the period):

No Of Active Employees (End of Priod) = 
var __maxPeriod = MAXX( VALUES( 'calendar'[Date] ) , 'calendar'[Date] )
return
CALCULATE(
    DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])
    , 'calendar'[Date] = __maxPeriod
)

Hopefully, this provides what you are looking for.

If not, please create a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well and please start a new question. This will avoid confusion about the topic of this question. 

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User II
Super User II

Hey @WorkHard ,

 

here you will find a solutions that provides a solution to your challenge:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EZeJfxufOaBJmhxXRyd9cUwBe0iRe...

The challenge you are facing has a name: event-in-progress.

There are a lot of blog posts about this problem available. Please be aware that my solution follows a different approach, as I expand the table that contains the start and end date in a way that the new table also has a single date column, in my solution I use this DAX statement to expand the base table:

EmployeesActivityDays = 
GENERATE(
    'EmployeesActivity'
    , var _DateStart = 'EmployeesActivity'[Start Date]
    var _DateEnd = 'EmployeesActivity'[End Date]
    return
    GENERATESERIES(_DateStart , _DateEnd , 1)
)

Please be aware that I added a column that allows to differentiate the activities.

The above approach allows to leverage the power of filter propagation by relating the calendar table to the date column of the new table.

 

There is a very simple measure:

No Of Active Employees = 
DISTINCTCOUNT('EmployeesActivityDays'[Employee Name])

 

This allows to create two table visuals like so:

image.png

Don't be afraid of expanding the table and creating a table with hundreds of thousands to tenth of millions of rows, basically scanning a simple table always  performs much better than evaluating "compex" formulas, where different columns are combined forcing query materialization.

 

Hopefully this provides what you are looking for

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

That is a very cool approach @TomMartens , thanks for sharing!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hey @JarroVGIT ,


thanks.

 

My pleasure,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors