Monthly FTE (Fulltime Equivalent) headcount reporting when employees change status in the month

[ashnoti]

Hi Gurus

I apologise in advance if I have made any beginner errors in my question below as I have never posted a question.

I have built a headcount (FTE) report based on daily hours table that contains an employee number, Employee status (Permanent/Casual/Agency), Hours for each hour type (Normal, Overtime etc.), Date worked. I also have a Date table that assigns dates to a month based on a 4-4-5 basis and financial year (July to June). I have a [Report Days] Measure that calculates the number of working days in the user selected date range.

Sample data and Pbix: pbix file sample data 

The FTE calculation is based on the employee's status: 

1. Perm it is simply a distinct count of the employee number (Disregards the hours worked).

2. For any other status it is total hours divided by the number of standard hours in a month (e.g. October is a 4 week month and standard hours is 152hrs, if a casual works 100Hrs the head count would be 100/152 = 0.66 of a head (Fulltime Equivalent).

Perm = IF Employee Status = "Perm", Distininct count of employee number within the date range selected.

               ELSE DIVIDE(Total worked hours, Standard hours in date range,0)

Problem: In some months an employee has hours in Perm as well as Casual which means they are counted as 1 under Perm as well as under Casual based on the hours worked (Overstated FTE). The rule I wish to apply is as follows:

Over the user selected date range, IF an employee has hours under Perm AND any other Labour Group, then for each Employee FTE = DIVIDE(Total Hours,(Report Days]*7.6) with no distinction of Labour Group.

Currently I see the same employee under Perm and Casual:

I want the report to show 27/152 = 0.18 FTE under Perm and 61/152=0.40 under Casual.

Relationships: Dates table and Hours Report Table - [Date Key]-[Date]

[v-tangjie-msft]

Hi @ashnoti ,

I don't quite understand your needs , please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[ashnoti]

Hi Neeko @v-tangjie-msft 

Thank you for your reply and I apologise for the delay in getting back to you.

Pibx:Labour Report.pibx 

Data:Labour Data.xlsx 

Have a look at employee 444009 - they changed from Casual to Part Time and then back to Casual in October.

I would like for the report to show this person as Casual as most of the hours in October were casual hours; like this:

Sep

Oct

Total

Labour Goup

Employee

Work Days

Total Hrs

FTE-Non Perm

FTE - Perm

Work Days

Total Hrs

FTE-Non Perm

FTE - Perm

Work Days

Total Hrs

FTE-Non Perm

FTE - Perm

Casual

444009

25

147

0.77

20

79.25

0.52

45

226

0.66

Also I would like to have just one column for FTE that shows Distinctcount of Employee ID if Perm and [Total Hours]/(Work Days * 7.6) if not Perm:

		Sep			Oct			Total
Labour Goup	Employee	Work Days	Total Hrs	FTE	Work Days	Total Hrs	FTE	Work Days	Total Hrs	FTE
Casual	444009	25	147	0.77	20	79.25	0.52	45	226	0.66
Perm	444019	25	193.75	1	20	152	1	45	345.75	1

Does this help?