FTE calculation taking into account working days in a month

[Anonymous]

Hello,

I'm a new user and I saw that you helped a lot of people

Could you please help me out with my problem?

I have an employee database and I would like to have a my visual report showing FTE per month.

On the database, I have start and end date and FTE per employee.

I have created a Table Date.

My current code is the following:

FTE Actual =

CALCULATE(

sum('FTE database'[FTE]),

FILTER(VALUES('FTE database'[Hire Date]),'FTE database'[Hire Date]<=MAX('Table Dates'[Date])),

FILTER(VALUES('FTE database'[Termination Date]),OR('FTE database'[Termination Date]>=MIN('Table Dates'[Date]),ISBLANK('FTE database'[Termination Date])))

)

This is working except for people terminated the first of the month, they are counted as 1 FTE. 

How can I take into account the working days in the month?

Thanks a lot!

Rgds

Yen-Ly

[amitchandak]

@Anonymous , try with Max

FTE Actual =
CALCULATE(
sum('FTE database'[FTE]),
FILTER(VALUES('FTE database'[Hire Date]),'FTE database'[Hire Date]<=MAX('Table Dates'[Date])),
FILTER(VALUES('FTE database'[Termination Date]),OR('FTE database'[Termination Date]>=Max('Table Dates'[Date]),ISBLANK('FTE database'[Termination Date])))
)

also refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak 

Thanks a lot for your help!!

It's working 

I miss just the precision when someone starts or ends during the month, it is rounded to 1, while the FTE should be prorated to the working days per month.

If you have something for this, it would be great, otherwise I'll live with it

In excel the formula I used was quite complex.

Regards

Yen