Calculating correct FTE

[JB_AT]

Hi

I have a table with EmpID, FTE, FromDate, ToDate. 

There are multiple rows for each employee and the FTE for that employee appears multiple times, so my runningtotal returns the wrong result. This is due to record ending and starting in the same month and the RT calculate both Values.

I thought I had solved the issue by getting the most recent FTE with this measure and using this in my Running Total measure, but it doesn't consider all records.

FTE = 
    CALCULATE(
        LASTNONBLANK(Employment[FTE], 1)

 
Does anyone know how I can approach this issue?

Thanks

[Anonymous · Accepted solution]

Hi @JB_AT 

@Sahir_Maharaj  Good share! Here we provide another method:

Here's some dummy data

“DATE”

“Employment”

Create measures. Querying the "FTE" for the latest date,

VALUE_FTE = var max_date = MAX('DATE'[Date])
 VAR FromDate = SELECTEDVALUE(Employment[FromDate])
 var ToDate = SELECTEDVALUE(Employment[ToDate])
 var fteeee = SELECTEDVALUE(Employment[FTE])
 var result_fte =  IF(FromDate <= max_date && ToDate >= max_date, fteeee, BLANK())
RETURN result_fte

Calculate the FTE, and here is the result.

RESULT = CALCULATE(sum(Employment[FTE]), FILTER(ALL(Employment), 'Employment'[FTE] = [VALUE_FTE]))

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Best Regards,

Nono Chen

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

[Sahir_Maharaj]

Hello @JB_AT,

Your current method using LASTNONBLANK retrieves the most recent FTE value, but it might not accurately account for all records, especially in cases of overlapping/concurrent date ranges. Can you please try this:

Daily FTE = 
CALCULATE(
    SUM(Employment[FTE]),
    FILTER(
        Employment,
        Employment[FromDate] <= MAX(DateTable[Date]) &&
        Employment[ToDate] >= MAX(DateTable[Date])
    )
)