Adjusting measure to identify changes in FTE value when the role remains the same.

[Oberon]

Hello All,

I recently had this post successfully solved. I'm referencing it as the solution has a working PBIX which I can't add. (Too new)

I've expanded the logic to my uncleansed data and it worked as intended. I then decided to add FTE data and that highlighted a scenario I hadn't included in my initial request which wasn't calculating as intended.

I have some employees which have a change in FTE values but stay in the same role. In these cases the employees return a 0 in the measure and don't appear before the latest role change and then sum both the FTE values after the latest role change. I can get the FTE to calculate correctly with a measure once it appears but I can't figure out how to modify the measure from the previous solution so they appear before the latest role.

So the updated table would look like this:

(Only difference from previous post is that employee B now retains the same 'Role' but moves to 0.6 FTE )

In this case I get.

After May:

Before May:

Like mentioned above, I can fix the FTE count after May, just not sure how to get an employee to appear before May without ruining the logic for the rest of table.

Here is the code provided by v-xuxinyi-msft in teh last post to be placed as a visual filter.

Latest Role Filter = 
VAR _MaxDate = CALCULATE(MAX([Date]), FILTER('EmploymentHistory', [Date] <= MAX('Date'[Date]) && [End Date] > MAX('Date'[Date]) || [Date] <= MAX('Date'[Date]) && [End Date] = BLANK()))
RETURN
IF(
    SELECTEDVALUE(EmploymentHistory[Employee ID]) = BLANK() && MAX([Date]) = _MaxDate, 1, IF(
        MAX([Date]) = _MaxDate && MAX([Employee ID]) = SELECTEDVALUE(EmploymentHistory[Employee ID]), 1, 0)
    )

If anyone knows how to update the code or can suggest additional measures/columns to get the role appearing before the latest role change it would be much appreciated.

[Anonymous · Accepted solution]

Hi @Oberon ,

My Sample:

I have a test on myside, I suggest you to try code as below to create a FTE measure directly.

M_FTE = 
VAR _RangeStart =
    MIN ( 'Date'[Date] )
VAR _RangeEnd =
    MAX ( 'Date'[Date] )
VAR _Filter1 =
    FILTER (
        ALLSELECTED ( EmploymentHistory ),
        EmploymentHistory[Date] <= _RangeEnd
            && OR (
                EmploymentHistory[End Date] >= _RangeStart,
                EmploymentHistory[End Date] = BLANK ()
            )
    )
VAR _Filter2 =
    FILTER (
        _Filter1,
        VAR _EMID = [Employee ID]
        VAR _MAXDATE =
            MAXX ( FILTER ( _Filter1, [Employee ID] = _EMID ), [Date] )
        VAR _Role =
            MAXX ( FILTER ( _Filter1, [Employee ID] = _EMID && [Date] = _MAXDATE ), [Role] )
        RETURN
            [Role] = _Role
    )
RETURN
    SUMX (
        FILTER (
            _Filter2,
            [Employee ID] = MAX ( EmploymentHistory[Employee ID] )
                && [Role] = MAX ( EmploymentHistory[Role] )
        ),
        [FTE]
    )

Result is as below.

After May:

Before May:

Best Regards,
Rico Zhou

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

[Oberon]

Thank you Rico Zhou, this works well.

If anyone else is looking to run a similar scenario as me and is struggling to finish the last bit where it sums both the part and full time FTE values on the month where the change occurs I added the following to Rico's solution.

Created a calculated column:

FT/PT = if([FTE]=1,"FT","PT")

Then another 2 measures:

MeasureFTPT = 
LASTNONBLANK(EmploymentHistory[FT/PT],maxx(EmploymentHistory,EmploymentHistory[Employee ID]))

and

M_FTE2 = 
if(
    and([MeasureFTPT] = "PT",[M_FTE] > 1),
    [M_FTE]-1,
    if(
        and([MeasureFTPT] = "FT",[M_FTE] > 1),
        1,
        [M_FTE]
        )
    )

Then just use M_FTE2 in place of FTE in the table columns and everything should work.