Calculating FTE per department with Department movements

[RobBeijers312]

Hi, 

I've got two tables with employee data. One of them has the contractdata and current department, the other has all the department moves of the employees.

Contractdata table example:

Name	Employeeid	Contractstartdate	Contractenddate	Current department	FTE
Frank	1	1-11-2023		Finance	1,00
Adam	2	1-1-2022		Finance	0,80
Ryan	3	1-1-2024	31-8-2024	Sales	1,00
Vera	4	1-6-2024		Purchase	0,60
Stefan	5	1-1-2023		Purchase	1,00
Simon	6	1-1-2023		Sales	0,80
Shannon	7	1-1-2022	31-3-2024	Finance	1,00

Department moves table:

Name	Employeeid	Startdate	Enddate	Department	FTE
Frank	1	1-11-2023		Finance	1,00
Adam	2	1-1-2022	31-1-2023	Sales	0,80
Adam	2	1-2-2023		Finance	0,80
Ryan	3	1-1-2024	31-8-2024	Sales	1,00
Vera	4	1-6-2024		Purchase	0,60
Stefan	5	1-1-2023	31-8-2023	Finance	1,00
Stefan	5	1-9-2023		Purchase	1,00
Simon	6	1-1-2023	30-4-2024	Purchase	0,80
Simon	6	1-5-2024		Sales	0,80
Shannon	7	1-1-2022	30-6-2023	Sales	1,00
Shannon	7	1-7-2023	31-3-2024	Finance	1,00

[V-yubandi-msft]

Hi @RobBeijers312 ,

Thank you for contacting the Microsoft Fabric community. Could you kindly try the following.

FTE_2023_Measure =
VAR Start2023 = DATE(2023, 1, 1)
VAR End2023 = DATE(2023, 12, 31)
VAR DaysIn2023 = 365
 
RETURN
SUMX(
    FILTER(
        'Department_moves',
        NOT(ISBLANK([Startdate])) &&
        ([Startdate] <= End2023) &&
        (ISBLANK([Enddate]) || [Enddate] >= Start2023)
    ),
    VAR EffectiveStart = IF([Startdate] < Start2023, Start2023, [Startdate])
    VAR EffectiveEnd = IF(ISBLANK([Enddate]) || [Enddate] > End2023, End2023, [Enddate])
    VAR DaysActive = DATEDIFF(EffectiveStart, EffectiveEnd, DAY)+ 1
    VAR FTEValue = SUBSTITUTE(Department_Moves[ FTE ],",",".")*1
    RETURN (DaysActive * FTEValue) / DaysIn2023
)
 
 
 
 
 
 
 
 
FTE_2024_Measure =
VAR Start2024 = DATE(2024, 1, 1)
VAR End2024 = DATE(2024, 12, 31)
VAR DaysIn2024 = 366
 
RETURN
SUMX(
    FILTER(
        'Department_moves',
        NOT(ISBLANK([Startdate])) &&
        ([Startdate] <= End2024) &&
        (ISBLANK([Enddate]) || [Enddate] >= Start2024)
    ),
    VAR EffectiveStart = IF([Startdate] < Start2024, Start2024, [Startdate])
    VAR EffectiveEnd = IF(ISBLANK([Enddate]) || [Enddate] > End2024, End2024, [Enddate])
    VAR DaysActive = DATEDIFF(EffectiveStart, EffectiveEnd, DAY) + 1
    VAR FTEValue = SUBSTITUTE(Department_Moves[ FTE ],",",".") * 1
    RETURN (DaysActive * FTEValue) / DaysIn2024
)

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.

[RobBeijers312]

Hi,

@V-yubandi-msft 

The measure works, but is it possible to combine them into one measurethat works for all years.

At the moment the measure doesn't reference the date table, wich is needed for the report.

Thanks in advance!

[V-yubandi-msft · Accepted solution]

Hi @RobBeijers312 ,

Please try to use the below mentioned measure and if you filter the year using the slicer , then the measure will give the result based on the current selection.

FTE_Measure_Dynamic =

VAR SelectedYear = SELECTEDVALUE('DateTable_FTE'[Year],2024) -- Default to 2024 year if no selection

VAR StartDate = MINX(FILTER('DateTable_FTE', YEAR('DateTable_FTE'[Date]) = SelectedYear), 'DateTable_FTE'[Date])

VAR EndDate = MAXX(FILTER('DateTable_FTE', YEAR('DateTable_FTE'[Date]) = SelectedYear), 'DateTable_FTE'[Date])

VAR TotalDays = COUNTROWS(FILTER('DateTable_FTE', YEAR('DateTable_FTE'[Date]) = SelectedYear))

RETURN

SUMX(

    FILTER(

        'Department_moves',

        NOT(ISBLANK([Startdate])) &&

        ([Startdate] <= EndDate) &&

        (ISBLANK([Enddate]) || [Enddate] >= StartDate)

    ),

    VAR EffectiveStart = MAX([Startdate], StartDate)

    VAR EffectiveEnd = MIN(IF(ISBLANK([Enddate]), EndDate, [Enddate]), EndDate)

    VAR DaysActive = DATEDIFF(EffectiveStart, EffectiveEnd, DAY) + 1

    VAR FTEValue = SUBSTITUTE(Department_Moves[ FTE ], ",", ".") * 1

    RETURN (DaysActive * FTEValue) / TotalDays

)

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[RobBeijers312]

Thanks, it works!

[RobBeijers312]

The expected result:

See two examples below.

Employee 1 - Frank:

2023 = 61 days / 365 days * 1,00 FTE = 0,17 FTE Finance

2024 = 366 days / 366 days * 1,00 FTE = 1,00 FTE Finance

Employee 6 - Simon:

2023 = 365 days / 365 days * 0,80 FTE = 0,80 FTE Purchase

2024 = 121 days / 366 days * 0,80 FTE = 0,26 FTE Purchase

2024 = 245 days / 366 days * 0,80 FTE = 0,54 FTE Sales

The employeeid of contractdata has a one to many relationship towards employeeid of departmentmoves. There are no relationships between the date table and both tables.

Can someone help me with this measure?