Forum Discussion

JB_AT's avatar
JB_AT
Helper III
2 years ago
Solved

Calculating correct FTE

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 resu...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Sahir_Maharaj
Super User
2 years ago

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])
    )
)