topic Re: Employee FTE count from partial days worked in month in DAX Commands and Tips

Employee FTE count from partial days worked in month

RinksBI — Mon, 22 Nov 2021 18:34:28 GMT

Hi,

I am trying to calculate the employee count (FTE) based on the start(hire) date, end(terminate) date, and also considering the partial number of days worked in a month. I have put the information in a table to demonstrate that I am trying to achieve the numbers in the blue cells. I have an Employee table and a Date Table in my Power BI file where there is an active relationship between the Date Table-date and Employee table-start date and an inactive relationship between date Table-date and Employee table-end date.

I will be grateful if someone can help me with creating the right measure for calculating the "Total days worked" in a Month for each employee and the "FTE per month"

Re: Employee FTE count from partial days worked in month

Jihwan_Kim — Tue, 23 Nov 2021 04:43:41 GMT

Hi,

Please check the below picture and the attached pbix file.

I suggest having a disconnected DATE Table like below.

All measures are in the attached pbix file.

Total Days Worked: =

CALCULATE (

COUNTROWS ( 'Dates disconnected' ),

FILTER (

'Dates disconnected',

'Dates disconnected'[Date] >= SELECTEDVALUE ( Employee[Start Date] )

&& OR (

'Dates disconnected'[Date] <= SELECTEDVALUE ( Employee[End Date] ),

SELECTEDVALUE ( Employee[End Date] ) = BLANK ()

)

FTE measure: =

VAR totalcalendardayscount =

COUNTROWS ( 'Dates disconnected' )

VAR totalworkingdayscount =

SUMX ( Employee, [Total Days Worked:] )

RETURN

DIVIDE ( totalworkingdayscount, totalcalendardayscount )

Re: Employee FTE count from partial days worked in month

RinksBI — Tue, 23 Nov 2021 17:30:32 GMT

Thank you so so much for your generous help and for putting the effort into this.

Re: Employee FTE count from partial days worked in month

RinksBI — Wed, 24 Nov 2021 00:11:20 GMT

One small question, if I want to calculate the YTD value for both Total Days Worked and FTE measure, should I create an inactive relationship between Date Disconnected table and Employee table and then use USERELATIONSHIP function?

I tried creating the YTD measure without any relationship but it is not working.

Re: Employee FTE count from partial days worked in month

Jihwan_Kim — Wed, 24 Nov 2021 04:03:46 GMT

Hi,

Can you let me know the expected outcome?

Because I decided to use a disconnected date table, I think the measure has to be written differently than using the time intelligent function.

Re: Employee FTE count from partial days worked in month

RinksBI — Wed, 24 Nov 2021 18:10:54 GMT

This is the YTD value I need to generate.

Thank you again for responding.

Re: Employee FTE count from partial days worked in month

RinksBI — Mon, 29 Nov 2021 18:08:43 GMT

Hi, sorry for bothering you again, do you think this can be done?

I attached a sample outcome in my last response.