Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.