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
Anonymous
Not applicable

Calculate FTE prorata based on hiring and leaving date

Hi people, 

I need some help to calculate a prorata FTE Based on employee hiring date and leaving date. 

 

The calc is : 

prorata of the FTE = (worked days*contractual FTE)/month working days

 

Example:

March 2019 had 21 working days (monday to friday , 0 bank holidays)

April 2019 had 20 working days (monday to friday exculing 19/04 and 22/04 was bank holidays)

 

 

Emp 1122 hired 11/03/2019, her contractul FTE is 100;

She had 15 working days (from 11/03 up to 31/03/2019);

So her pro rata FTE in March is = (15*100)/21 = 71 and in April is 100 and for May if shw won't leave will be 100.

 

 

Emp 1123 hired 08/04/2019, his contractual FTE is 100;

He had 15 working days (from 08/04/2019 up to 30/04/2019);

so his pro rata FTE in April= (15*100)/20 = 75, in May if he won't leave the company he would have 100;

 

Emp 1124 hired 15/04/2019, her contractual FTE is 60;

She had 10 working days (from 08/04/2019 up to 30/04/2019);

So her pro rata FTE in April is = (10*60)/20 = 30 , in May if she won't leave the company she would have 60; 

 

If employee left in the during the month the same thinking should be applied, calculate the entitle working days based on leaving date. 

 

I have calculate a mesure for working days per month, and i need some help to calculate the pro rata FTE per employee and the total for each month. 

 

I have add an example of the file that i am working on and the measures that i have created so far. 

https://www.dropbox.com/s/5cqgu1rsbxzzrnd/Example%20-%20FTE%20prorata.pbix?dl=0

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have found the solution. 

 

1. I have create 2 calculate column on my data base which will have the working days for each line. 

 

Worked days = CALCULATE(SUM('CALENDAR'[WDay]), DATESBETWEEN('CALENDAR'[Date], Data_Payroll[START_DATE],Data_Payroll[END_DATE]))
Month Working days = CALCULATE(SUM('CALENDAR'[WDay]),DATESBETWEEN('CALENDAR'[Date],STARTOFMONTH('CALENDAR'[Date]), ENDOFMONTH('CALENDAR'[Date])))
FTE_partial = ([FTE_Real]*Data_Payroll[Worked days])/Data_Payroll[Month Working days]

 

2.  I have create the following measures (as I've tryied to create a nest measure but the result was not the expected)

 

.Working_days_Employee = IF(
    HASONEFILTER('Data_Payroll'[Payroll nb]),
    MAX(Data_Payroll[Worked days]),
   SUM(Data_Payroll[Worked days]))
.Working_days_Month = MAX(Data_Payroll[Month Working days])
.Month_select = 
VAR Month= SELECTEDVALUE('CALENDAR'[Month Nb])
VAR Selected = IF(HASONEFILTER('Calendar'[Month]), 
                Month, 
                MONTH(LASTDATE(Data_Payroll[Reference])))
RETURN
Selected
.FTE_Month_partial = SUM(Data_Payroll[FTE_partial])
.FTE_Year_partial = 
SUMX(
     VALUES('Calendar'[Month]),
     [.FTE_Month_partial])
    /[.Month_select]

 and Finaly 

FTE_Partial = IF(HASONEFILTER('CALENDAR'[Month]),[.FTE_Month_partial], [.FTE_Year_partial])2  

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

I have found the solution. 

 

1. I have create 2 calculate column on my data base which will have the working days for each line. 

 

Worked days = CALCULATE(SUM('CALENDAR'[WDay]), DATESBETWEEN('CALENDAR'[Date], Data_Payroll[START_DATE],Data_Payroll[END_DATE]))
Month Working days = CALCULATE(SUM('CALENDAR'[WDay]),DATESBETWEEN('CALENDAR'[Date],STARTOFMONTH('CALENDAR'[Date]), ENDOFMONTH('CALENDAR'[Date])))
FTE_partial = ([FTE_Real]*Data_Payroll[Worked days])/Data_Payroll[Month Working days]

 

2.  I have create the following measures (as I've tryied to create a nest measure but the result was not the expected)

 

.Working_days_Employee = IF(
    HASONEFILTER('Data_Payroll'[Payroll nb]),
    MAX(Data_Payroll[Worked days]),
   SUM(Data_Payroll[Worked days]))
.Working_days_Month = MAX(Data_Payroll[Month Working days])
.Month_select = 
VAR Month= SELECTEDVALUE('CALENDAR'[Month Nb])
VAR Selected = IF(HASONEFILTER('Calendar'[Month]), 
                Month, 
                MONTH(LASTDATE(Data_Payroll[Reference])))
RETURN
Selected
.FTE_Month_partial = SUM(Data_Payroll[FTE_partial])
.FTE_Year_partial = 
SUMX(
     VALUES('Calendar'[Month]),
     [.FTE_Month_partial])
    /[.Month_select]

 and Finaly 

FTE_Partial = IF(HASONEFILTER('CALENDAR'[Month]),[.FTE_Month_partial], [.FTE_Year_partial])2  

 

v-shex-msft
Community Support
Community Support

HI @Anonymous ,

You can try to use following measures formula if they works for your requirement:

WD CMonth Except Holiday = 
COUNTROWS (
    FILTER (
        EXCEPT (
            CALENDAR ( MIN ( 'CALENDAR'[Date] ), MAX ( 'CALENDAR'[Date] ) ),
            ALL ( 'Bank Holidays'[DATE] )
        ),
        WEEKDAY ( [Date], 2 ) < 6
    )
)

FTE prorata = 
VAR list = { 376, 377, 445 }
VAR summary =
    SUMMARIZE (
        FILTER (
            PAYROLL,
            NOT ( Payroll[Payroll nb] IN list )
                && PAYROLL[Most Recent Hire Date] <=MAX( 'CALENDAR'[Date] )
                && OR (
                    PAYROLL[Administrative End Date] >= MAX ( 'CALENDAR'[Date] ),
                    PAYROLL[Administrative End Date] = BLANK ()
                )
        ),
        PAYROLL[FTE],
        "WD", COUNTROWS (
            FILTER (
                CALENDAR (
                    MIN(MIN(PAYROLL[Most Recent Hire Date]),MIN('CALENDAR'[Date])),
                    MAX('CALENDAR'[Date])
                ),
                WEEKDAY ( [Date], 2 ) < 6
            )
        )
    )
RETURN
   SUMX ( summary, [FTE] * [WD] / [WD CMonth Except Holiday] )

21.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft , 

 

Thanks for you reply, it doesnt worked for what i nee, but helped me to figure out a "solution". 

I have create a measure with variable to calculate the worked days for new hiring and for leavers: 

Emp Worked Days = 
VAR EmpHiringDate = SELECTEDVALUE(PAYROLL[Most Recent Hire Date])
VAR EmpLeaveDate = SELECTEDVALUE(PAYROLL[Administrative End Date])
VAR MonthLastDAY = LASTDATE('CALENDAR'[Date])
VAR MonthFirstDAY = FIRSTDATE('CALENDAR'[Date])
VAR HiringWorkDay = CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], EmpHiringDate, MonthLastDAY))
VAR LeaveWorkDay = IF(ISBLANK(EmpLeaveDate),0,IF(EmpLeaveDate>=MonthFirstDAY,CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], MonthFirstDAY, EmpLeaveDate)),CALCULATE(SUM('CALENDAR'[W Day]),DATESBETWEEN('CALENDAR'[Date], EmpHiringDate, EmpLeaveDate))))
VAR EmpWksDay = IF(MONTH(EmpLeaveDate)=SELECTEDVALUE('CALENDAR'[Month number]) && YEAR(EmpLeaveDate)=SELECTEDVALUE('CALENDAR'[Year]),LeaveWorkDay,IF(HiringWorkDay>=[Working days],[Working days],HiringWorkDay))
RETURN
EmpWksDay

Then I have create a new measure to calculate the pro-rata

FTE prorata = SUMX(VALUES('Calendar'[Month]),([Emp Worked Days]*[HC - FTE])/[Working days])

It's returning the correct figure for each line (employee) but the total is wrong.  For FTE prorata, for the displayed employees the total should be 1.71 

ScreenshotScreenshot

 

Do you know what is wrong ? 

Hi @Anonymous ,

Actually, this is a common issue for measures. Normally you will use some functions to get current row content for measure calculation.
For dax functions you used to get current row contents(e.g. max, sum ,min), they will return different value on different summary level.(when you use on total level, they may get wrong value from summarize row contents)

In my opinion, I'd like to suggest you add a variable table with summarize function to 'fix' each row result of measure and sue iteration function to summary them to get correct value.

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft , 

 

I am trying to using the formula that is on the topic that you sent m, but i don't understand why it's not working. I have edited the formula to match my data, but it's still returning the same wrong total.

 

m_Total 1 = 
VAR __table = SUMMARIZE('CALENDAR',[Month],"__value",[FTE prorata])
RETURN
IF(HASONEVALUE('CALENDAR'[Month]),[FTE prorata],SUMX(__table,[__value]))

2019-05-08 17_57_58-Example - FTE prorata - Power BI Desktop.png

As you can see the total is counting not summing.

 

I am getting mad if this figures Man Sad

 

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.