Calculating FTE difference related to a department in time

[Mike89]

Hello everyone,

In a FTE report I like to calculate the difference in FTE movement on a specific employee ID within a specific department.

Example data:

Date                                      Empl_ID              FTE         Department

01-01-2022                         Empl1                   0.8          1            

01-01-2022                         Empl2                   1.0          2

01-01-2022                         Empl3                   1.0          3

01-01-2022                         Empl4                   1.0          4

01-02-2022                         Empl1                   1.0          1

01-02-2022                         Empl2                   1.0          2

01-02-2022                         Empl3                   1.0          3

01-02-2022                         Empl4                   1.0          4

Result

So the result will be that empl1 is working 0.2 FTE more in Februari on department 1.

Department:                     Changed in FTE

1                                             0.2         

2                                             0

3                                             0

4                                             0

I hope you can help me I don’t know which DAX combination I should use here.

Regards Mike

[v-rzhou-msft · Accepted solution]

Hi @Mike89 ,

I think you can try this code to create a measure.

Changed in FTE = 
VAR _LATEST_DATE =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Empl_ID] ) )
VAR _PREVIOUS_DATE =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Empl_ID] ), 'Table'[Date] < _LATEST_DATE )
    )
VAR _LATEST_FTE =
    CALCULATE (
        SUM ( 'Table'[FTE] ),
        FILTER ( 'Table', 'Table'[Date] = _LATEST_DATE )
    )
VAR _PREVIOU_FTE =
    CALCULATE (
        SUM ( 'Table'[FTE] ),
        FILTER ( 'Table', 'Table'[Date] = _PREVIOUS_DATE )
    )
RETURN
    _LATEST_FTE - _PREVIOU_FTE

Result is as below.

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.