Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors