cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RinksBI
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"

 

RinksBI_0-1637606053464.png

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

 

Picture1.png

 

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.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
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.

 

Picture1.png

 

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.


Go to My LinkedIn Page


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.

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.


Go to My LinkedIn Page


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

I attached a sample outcome in my last response.

This is the YTD value I need to generate.

Thank you again for responding.

 

RinksBI_0-1637777424144.png

 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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

May UG Leader Call Carousel 768x460.png

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