Headcount Snapshot and attrition issue

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! 🙂