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

 

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.

     

     

     

2 Replies

  • 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])
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.