Count Amount of Active Employees by period

[WorkHard]

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)

[Bertverderrie]

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?

[TomMartens]

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

[GILLOJUNKUMAR]

HI,

I want to find the active manager with designation of manager and Sr. Manager.

ACTIVE MANAGER =

var currentdate =

        MAX('CALENDAR DATE'[Date])

return

        CALCULATE(

            COUNTROWS(EMPLOYEE_D),

            FILTER(EMPLOYEE_D,(EMPLOYEE_D[DATE_OF_JOINING] <= currentdate

                && EMPLOYEE_D[DATE_OF_RELIEVING] >= currentdate), ALLSELECTED(                 EMPLOYEE_D[ACTIVE_FLG] = "Y", EMPLOYEE_D[DESIGNATION] in {"Manager", "sr.Manager"})))

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TomMartens · Accepted solution]

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:

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

[JarroVGIT]

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

[TomMartens]

Hey @JarroVGIT ,

thanks.

My pleasure,

Tom