Employee FTE count from partial days worked in month

[RinksBI]

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"

[Jihwan_Kim · Accepted solution]

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 )

[RinksBI]

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.

[Jihwan_Kim]

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.

[RinksBI]

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

I attached a sample outcome in my last response.

[RinksBI]

This is the YTD value I need to generate.

Thank you again for responding.

[RinksBI]

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