Employee Headcount Rolling over Year-Month

[rush]

I would like to count the number of people we would have had in a Month for a particular Year.

The measure that I have is not counting correctly:

Headcount Movement = CALCULATE(
  COUNTROWS(Staff),
  FILTER(Staff,
  AND(Staff[Date Employed].[Date]<MIN(Dim_Date[Calendar_Date]),
      Staff[Staff_Termination_Date].[Date]>MAX(Dim_Date[Calendar_Date]))))

I currently have a table called Staff with columns:

StaffID
DateEmployed
Staff_Termination_Date
Team Member Status

Then I have Date Dimension that is not directly connected to my Staff table.

The formula would count every employee with a DateEmployed then stop counting them when their Staff_Termination_Date is in the past based on the Date Dimension table.

Sample Data

Thank you in advance. 

[haozhong]

Try create new column in  your date dimension and use the following formula for the new column:-

CountEmployee = CALCULATE(COUNTA(Staff[StaffID]),FILTER(ALL(Staff),(Staff[Staff_Termination_Date]>=DimDate[Date])))-CALCULATE(COUNTA(Staff[StaffID]),FILTER(ALL(Staff),(Staff[Date Employed]>=DimDate[Date])))

This will populate the number of staff by dates. DimDate is the name of my date dimension table and can be replace by your date dimension name.

Basically, it is the number of ALL employees with termination dates after the date in your date dimension MINUS ALL employees that start after the date in your date dimension. The result will show the number of staff that are still with company on the date in your date dimension but join before the date in your date dimension.

[rush]

@haozhong Hi, unfortunately, I do not have enough memory to compute that calculation.

Is there an alternative way & if possible to work out the Starters, Leavers & Net Growth per Year-Month?

[haozhong]

Oh dear 😞

Better do in excel then doing the manipulation using COUNTIFs. Then prepare everything in table format in excel. Then use Power BI as reporting tool. That's in my opinion only. Maybe other gurus can help you out

[rush]

@haozhong Hi, I managed to do it in excel. Thanks but my issue would be creating a relationship with my staff table that I would need to filter on.

Is there no other way to create the headcount from my Staff Table?

[fhill]

See this post I worked on recently.  They were looking for slicer data, but you coudl easily do the same thing to graph employee / salaries by date range...     I have my Date List as the Axis and the SUM of Salary per Month below.  (You would probably want a 'Unique Count of ?? Employee ID/Name??)

https://community.powerbi.com/t5/Desktop/Salaries-within-a-time-period/m-p/215862#M95564

[rush]

@fhill I have looked at your post but I cannot seem to make sense of what I would need to do.

Could you please assist me?

Your help is much appreciated.

[fhill]

See some sample data below, hopefully it looks simular to the columns your data.  Make sure the Date columns are both set to Date format.  The 'Date List' column below is a custom column based on the code in the screen shot.  It basically counts every day between DateEmployed and Term Date (or TODAY) if Term Date is null.  

Expand the list to show all the data, and make sure to format DateList as a date again.  That's all you have to do, now you can chart or slice the data as desired.  My third screen shot is a simple line graph with DateList as the Axis and 'Count of StaffID' as the Values.

FOrrest

[rush]

@fhill Thank you very much. It is almost working. Just that it is not adding up correctly where the headcount in May 2017 should have dropped by 9.

I had team members where they had DateTermination of "1899/12/31" which I created another custom column to future date those ones to "2050/12/31".

Is there another alternative to creating my headcount where I just use my Staff Table?

[fhill]

Please post some sample non employee sensative data we can use to duplicate your issue...?

[rush]

@fhill All the data needed should be in the Sample data except for the date dimension which I have calculated tabel for that:

Dim_Date = CALENDAR (DATE(1997,1,1), DATE(2080,12,31))

[rush]

@fhill I would count off the StaffID being unique.

Will have a look at it & see if I can make sense of it.

Thank you.