Measures for active and terminated employees

[serky]

Hi

I have just started using PP and need help. I need to calculate the number of current and terminated employees for a given month.
An employee is considered active at the beginning of a month if start date <= first day of month and termination date is blank or >= second day of the month.

My data table has employee code (unique), start date and termination date (date of termination or blank if still working). I also have a dimCalendar table with DateInt (every day as d/mm/yyyy 12:00:00 AM) and MonthInCalendar (1/10/2011, 1/11/2011 etc). Employee start date and termination date are the same format as DateInt. I have linked start date and termination date to DateInt (start date is a dashed line)

My measures are:
[Total employee] = distinctcount([Employee code])
[# terminations] = calculate([Total employee], not(isblank(employeetable[termination date]))

I have several questions:

1. when I filter the termination date in the PP table to July 2013, [Total employee] = 11 and [# terminations] = 82 yet when I use [# terminations] in a pivot table against July 2013 = 11. Why?

2. how do I calculate the number of active employees at the beginning of a month? I need to re-use the measure in a pivot table against 12 months in a year.

Thanks

 

[scottsen]

If at all possible, you will want to avoid that DISTINCTCOUNT. It certainly works, and is easier to understand, but the performance can get a bit unsavory.

It's not super clear to me if you have > 1 row in the data table... if you do, you can still make a measure work w/o distinctcount, it's just trickier.

In general, I would think about it like this: I am going to count rows in the employee table... how do I know if I should include them? I include them if they were working, within the date range of the current context.

=CALCULATE(COUNTROWS(Employees), FILTER(Employees, Employee[StartDate] <= MAX(Calendar[DateKey]) && (Employee[termDate] = BLANK() || Employee[termDate] > MAX(Calendar[DateKey])))

It's probably not quite right, but hopefully you get my drift?
The red MAX is saying they had to have started before the end of the current period (whatever that is... end of month, if the pivot table is by month).
The blue MAX clause is saying... "they haven't been fired, or they haven't been fired... yet". Cuz I used max, it would be end of month, beginning might make better sense? Depends how you want to count it... they were employeed for a partial month, but you are looking at the whole month...

Also see my post that has a similiar feel: Two methods to calculate Person-Hours | Tiny Lizard Be sure to read the comments, there are links to other similiar techniques.

 

[serky]

Hi scottsen

I have finally got this to work but I have another question re the detail. I have used the following formula:

[Total # - employee headcount start period]:=CALCULATE(countrows(dataEmployee), FILTER(dataEmployee, dataEmployee[start date]<=[Date selected] && (dataEmployee[Termination date]>[Date selected]||isblank(dataEmployee[Termination date]))

[Total # - employee headcount next month]=calculate([Total # - employee headcount start period],NEXTMONTH(LASTDATE(dimCalendar[MonthInCalendar])))

[Date selected] is the month/year selected by the user as the reporting month where [Date selected]:=LASTDATE(dimCalendar[MonthInCalendar])

The average of these results is 'headcount'.

If I display these formula in a pivot table (MonthInCalendar in the rows), the correct data is returned for each month except the last month which is blank for [Total # - employee headcount next month]. This is also causing a problem in a CUBE formula I'm using for the report as the 'headcount' does not calculate correctly as the 'next month' value is blank.

My questions are:

1. how do get the 'next month' employee headcount formula to work

2. why does the [Total # - employee headcount start period] return the correct monthly value for each month in the pivot table, when the formula is referring to [Date selected]? Does the date in the pivot table row 'overwrite' [Date selected]?

Thanks

 

[scottsen]

What do you "want" the formula to return for 'next month' on the last month (when... there is no next month). blank sounds correct to me.

I suspect you have a relationship between calendar and employee table, which might not be correct in your scenario (you don't really have a single day that correctly ties an employee to calendar... you have a range of dates, AND you want to use a [Date Selected] concept outside the relationship anyway). Nuke that relationship.

 

[serky]

Hello

I have checked the data:
1. there is no relationship between the calendar table (dimCalendar) and employee data (dataEmployee)
2. dataEmployee has data from 1981 until December 2013
3. if the user selects July 2013, I need the formulas to calculate headcount at the beginning of July 2013 and August 2013
4. if I create a pivot table with July 2013 in the middle of a numbers of dates in the rows, the headcount for August 2013 is correct
5. if I create a pivot table with July 2013 the last date in the rows, the headcount for August 2013 is blank
6. if I change the formula that calculates 'next month's' headcount from:

[Total # - employee headcount next month]=calculate([Total # - employee headcount start period],NEXTMONTH(LASTDATE(dimCalendar[MonthInCalendar])))

to

[Total # - employee headcount next month]:=CALCULATE(countrows(dataEmployee), FILTER(dataEmployee, dataEmployee[start date]<=[Date selected - next month] && (dataEmployee[Termination date]>[Date selected - next month]||isblank(dataEmployee[Termination date]))

where
[Date selected - next month]=NEXTMONTH(LASTDATE(dimCalendar[MonthInCalendar]))

the pivot table described in point 5, reports the correct headcount for August 2013 (the CUBE formula also calculates the correct result). For some reason, having NEXTMONTH in the original headcount formula does not work. Am I doing something wrong?

I also have another problem, the formula for 'next month's' headcount returns results for all future month's even though there is no data for that period ie - if an employee has not been terminated, they are included in the headcount. Given that I know the date of the last data (December 2013), what is the best way to capture this information and then ensure that the formula calculates BLANK for any dates post the last data date?

Thanks

 

[scottsen]

If you have no relationship between Calendar and Employee, then doing things like NEXTMONTH() against the Calendar table... isn't going to impact the Employee table. That is why the first formula won't work.