cancel
Showing results for
Did you mean:
Regular Visitor

## Employee FTE count from partial days worked in month

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"

1 ACCEPTED SOLUTION
Super User

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

6 REPLIES 6
Super User

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Regular Visitor

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.

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Regular Visitor

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

I attached a sample outcome in my last response.

Regular Visitor

This is the YTD value I need to generate.

Thank you again for responding.

Regular Visitor

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors