Headcount Using Hire Date & Term Date Power Query

goob90

New Member
Joined
Nov 12, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Every week I run a report from our HRIS and drop a CSV file into a folder that is connected to Excel via PQ. I use the cleaned-up data to "feed" pivot tables and use them in a dashboard.

I have no problem getting a current headcount, but I would like to be able to get a headcount for each month of the year, dating back to 2016.

The data includes employee id, employee name, hire date, termination date, and status. Below is a sample using fictional data.

Dashboard.xlsx
ABCDE
1Emp IDNameHiredate Term DateStatus
2101John Hermiston1/1/199912/31/2016Terminated
3104Mrs. Ahmad Donnelly7/1/20082/28/2022Terminated
4112Geo Ondricka10/8/201212/26/2018Terminated
5113Chester Walsh10/13/2003Active
6114Gilberto Bashirian11/17/2003Active
7117Abby Tremblay2/25/20029/13/2019Terminated
8118Chaim Boehm6/9/20143/13/2019Terminated
9119Jarred Smitham8/29/20143/13/2019Terminated
10120Miss Lauriane Luettgen8/29/20141/25/2019Terminated
11121Reid Dooley2/7/2016Active
12122Kristina Stanton5/9/2016Active
13124Ms. Annamarie Mertz4/3/20175/2/2019Terminated
14125Roman Heidenreich2/5/2018Active
15126Mrs. Dorian Heaney3/20/20196/30/2022Terminated
16127Gustave Rosenbaum4/8/201911/4/2019Terminated
17128Cullen Considine7/29/20193/20/2020Terminated
18129Jarvis Schinner9/3/2019Active
19130Chyna Stanton V11/8/2019Active
20131Daija McLaughlin12/22/20196/30/2022Terminated
21132Miss Jolie Smitham9/14/20201/1/2022Terminated
22133Sarai Sipes I12/14/2020Active
23134Lee Hayes4/13/2021Active
24135Terrence Maggio V5/24/20215/26/2021Terminated
25136Lia Volkman6/15/2021Active
26137Amelia Schamberger6/13/2022Active
27138Jay Casper III7/4/2022Active
28139Maximilian Aufderhar7/11/2022Active
29201Mrs. Vilma Kuhic8/12/1996Active
30203Donnie Johnston1/30/2017Active
31206Rico Mayert11/28/2005Active
32402Alanna Brakus3/24/2014Active
33409Jillian Collins9/15/20083/15/2016Terminated
34411Emerald Schuster6/17/20025/11/2017Terminated
35413Judson Hilll I10/13/2014Active
36414Emma Jerde II6/13/20167/27/2018Terminated
37415Anne Lehner6/13/20162/14/2022Terminated
38416Sally Veum1/30/20174/11/2022Terminated
39417Zoie Keebler5/22/20173/4/2019Terminated
40418Maye Walter1/2/2019Active
Sheet40


I've done a little research and it was suggested that I create a calendar table in PQ, which I have done, but I am not sure what I should do next. Any suggestions?
1661372356133.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top