Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Headcount

Hello,

 

I am needing some assitance with finding out headcount data broken down weekly, monthly, annually. I have two data tables: US_CORE_HR (bi-weekly hr data) and a calendar table which has the break down of fiscal weeks, months, years. 

I am not used to working with a data set that is biweekly. It does include a column for week beginning date and week ending date correspoding with the fiscal weeks. Here is what I am trying to accomplish:

 

Beginning Headcount: The number of distinct associates at the beginning of the date range selected. Note, this would include transfers into the building as by default their hire date would be greater than the beginning of the date range.

 

New Hire: the number of associates hired during the selected time frame.

Hire Date is greater than time frame start date

*at this time we are not tracking if "new hires" are re-hires. we can review this at a later date.

 

Terminated: the number of associates terminated during the selected time frame.

note: an associate can be both a new hire and terminated within the time frame selected.

 

Ending Headcount: Beginning Headcount + New Hires - Terminated.

 

Distinct Headcount: the number of associates on in the system during this time frame, regardless of status.

note: transfers out would show as a gap in the math since they are not terminated, however will not appear in time frames after they transferred out.

 

Here is what happens with I filter anything greater than one week at at time: 

 

 

 

 

 

Calculations:

Calculated Column: New Hire = IF(YEAR([WK_BEGIN_DATE]) = YEAR([HIRE_DATE]) && MONTH([WK_BEGIN_DATE])=MONTH([HIRE_DATE]) && DAY([WK_BEGIN_DATE])<= Day([HIRE_DATE]) && Day([WK_END_DATE])>= DAY([HIRE_DATE]),1)
 
Measure: Terminated =CALCULATE(COUNT(US_CORE_HR[WIN_NBR]),not(ISBLANK(US_CORE_HR[NEW_TERMINATION_DATE])))
 
Calculated Column: Beginning HC = IF(US_CORE_HR[HIRE_DATE]<US_CORE_HR[WK_BEGIN_DATE],1,0)
 
Measure: Distinct Headcount = DISTINCTCOUNT(US_CORE_HR[WIN_NBR])
 
Measure: Ending HC = [Beginning HC Sum]+[New Hire_X]-[Terminated]
 
any assistance is much appreciated.
Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you, but this does not work for my data set. I have read through that article already. The main difference is their employee data file is stationary, meaning it does not included historical data in the cadence of pay periods. It I worked with just one payroll file it would work. However, I work with a file that includes each pay periods data. This allowes me to caputer historical data week over week.