FTE calculation taking taking into account working days per 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

[v-kkf-msft]

Hi @Anonymous ,

How do you calculate the the middle of the month? If it is to take the middle value for all working days of the month, please try the following formula:

FTE Actual = 
VAR median_date =
    ROUND (
        CALCULATE (
            MEDIAN ( 'Table Dates'[DateNum] ),
            KEEPFILTERS ( 'Table Dates'[IsWorkingDay] = 1 )
        ),
        0
    )
VAR Month_middle =
    CALCULATE (
        MIN ( 'Table Dates'[Date] ),
        FILTER ( 'Table Dates', 'Table Dates'[DateNum] = median_date )
    )
RETURN
    CALCULATE (
        SUM ( 'FTE database'[FTE] ),
        FILTER (
            VALUES ( 'FTE database'[Hire Date] ),
            'FTE database'[Hire Date] <= Month_middle
        ),
        FILTER (
            VALUES ( 'FTE database'[Termination Date] ),
            OR (
                'FTE database'[Termination Date] >= Month_middle,
                ISBLANK ( 'FTE database'[Termination Date] )
            )
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Winniz,

thanks for your help, 

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.

In this visual, the start date is May 19 2021 and termination is February 28 2022.

In excel the formula I used was quite complex.

If you have a kind of solution, it would be great

Thanks,

regds

Yen

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved?

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

Please try the following formula:

SumFTE = SUM ( 'FTE database'[FTE] )

WorkingDayDiff = 
CALCULATE (
    SUMX (
        VALUES ( 'FTE database'[ID] ),
        (
            CALCULATE (
                COUNTROWS ( 'Table Dates' ),
                FILTER (
                    'Table Dates',
                    'Table Dates'[Date]
                        >= MAX ( MIN ( 'FTE database'[Hire Date] ), MIN ( 'Table Dates'[Date] ) )
                        && 'Table Dates'[Date]
                            <= IF (
                                ISBLANK ( MAX ( 'FTE database'[Termination Date] ) ),
                                MAX ( 'Table Dates'[Date] ),
                                MIN ( MAX ( 'FTE database'[Termination Date] ), MAX ( 'Table Dates'[Date] ) )
                            )
                )
            )
                / COUNT ( 'Table Dates'[Date] )
        )
    ),
    'Table Dates'[IsWorkingDay] = 1,
    FILTER (
        'FTE database',
        'FTE database'[Hire Date] <= MAX ( 'Table Dates'[Date] )
            && OR (
                'FTE database'[Termination Date] >= MIN ( 'Table Dates'[Date] ),
                ISBLANK ( 'FTE database'[Termination Date] )
            )
    )
)

FTE Actual = 
SUMX ( VALUES ( 'FTE database'[ID] ), [SumFTE] * [WorkingDayDiff] )

Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Usually you indicate working days in the calendar table in your data model.  You have  calendar table, right?

[Anonymous]

Hi,

Yes, I'm using a Table of Dates like this:

ADDCOLUMNS(CALENDAR(DATE(2018,1,1),TODAY()),

        "Year",YEAR([Date]),

        "Month",MONTH([Date]),

"Name of month",FORMAT([Date],"MMM YYYY"),

        "Name of day",FORMAT([Date],"DDD d MMM YY"))

But if someone is starting or leaving in the middle of the month, it is counted as 1 FTE, while I would like to have it pro rata the number of working days in a month.

Do you have a solution for this?

THanks

Yen

[lbendlin]

Wouldn't you then have to prorate this for everybody else too? How do you know if someone may or may not leave before the end of the month?

You will likely have to calculate the FTE per day.  (What if they leave before noon? 🙂  )