Headcount Snapshot and attrition issue

[Anonymous]

Hello All, 

I am trying to write some logic in Power query to be able to calculate a snapshot of headcount at any given point in time (current, last month, quarter end, one year ago, etc.), while also wanting to calculate attrition figures and a turnover percentage by quarter. I think the most trouble that I am having is getting the snaphsout headcount number at any givent point in time and the rest would fall into place.

Below is a snapshot of the fields I am working with (i think they are all self explanitory and Full name would be a unigue ID but let me know if further clarification is needed).

Any ideas or suggestions Power Bi Jedis? Thanks in advance for you guidance! 

[rohit_singh]

Hi @Anonymous ,

You could try something like this :

1) Load the data. My sample data consists of two rows, one for each employee. 
The first employee was hired on 01/01/2022 and terminated on 07/01/2022
The second employee was hired on 01/01/2022 and terminated on 16/01/2022

This means that in the final result, we should be seeing an overlap between the employees for the 1st 7 days of January, ie. the employee count should be 2 from 1st to 7th Jan.

2) Create a custom column to get the dates between hire date and termination date for each employee. This gives us a list of values which are in number format.

3) Expand the list and change format to date. You will now see multiple rows for each employee, one row for each day of employment between hire and termination date.

4) Group by on the date column and get a list of employees that were employed on a given day. 

5) Count the number of employees in the list. This will give you the headcount snapshot per day, which means for each day in the year, you can see a snapshot of the exact headcount.

6) Rename columns, remove the count column and get the final result

Here is the sample M-code. You can copy and paste this onto a blank query and see the above steps in detail.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUrPyACSBob6QGRkYGQE5FRUVgHJgsIikIQ5kkR2Tq5SrA5EX2F5Krq+1KISIFmckgYkDc2QJdLSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dept Name" = _t, #"Full Name" = _t, #"Hire Date" = _t, #"Job Title" = _t, Location = _t, #"Termination Date" = _t, #"Termination Reason" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept Name", type text}, {"Full Name", type text}, {"Hire Date", type date}, {"Job Title", type text}, {"Location", type text}, {"Termination Date", type date}, {"Termination Reason", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Hire Date])..Number.From([Termination Date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Custom"}, {{"Count", each _, type table [Dept Name=nullable text, Full Name=nullable text, Hire Date=nullable date, Job Title=nullable text, Location=nullable text, Termination Date=nullable date, Termination Reason=nullable text, Custom=nullable date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each List.Count([Count][Full Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Emp Date"}, {"Custom.1", "Num Emp"}})
in
    #"Renamed Columns"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Anonymous]

Hi @rohit_singh , I am trying the following but coming up short:

"

Tr 365 HC = CALCULATE(DistinctCOUNT('HCData'[Full Name]),DATESINPERIOD('Date'[Date],TODAY(),-1,YEAR))"

or 

"

30 HC = CALCULATE (

DISTINCTCOUNT ('Headcount - Trailing'[Employee Number]),'Headcount - Trailing'[Status] = "Active",'Headcount - Trailing'[eis Date.DayOffset] >= -30,'Headcount - Trailing'[eis Date.DayOffset] <= 0)"

(Dayoffset being today = 0, yesterday = -1, etc. )

Appreciate any guidance! Thank you!!

[Anonymous]

Hi Rohit, 

Thank you for responding I really appreciate it! Your method would work, however we have thousands of employees (some who have worked from the company inception of 25+ years ago) and so creating that many records for each employee (especially those with a lot of tenure) would just likely break the model. Do you by chance have any other ideas? Thanks again for your guidance!