FTE Human Resources DAX

[jbqrn]

Hi, 
I have data from Human Resources and I need to calculate the FTE (Full Time Employee) for any specific moment.
I have the Employee Code, the Starting Date and the Ending Date in the company. 
Connected with a date table with innactive connections (I use USERELATIONSHIP) to use the tables (no problem with that).

The problem is that I dont know how to calculate the FTE (in DAYS) I attach the formula that I use to calculate current employees

Nº employees=

VAR FechaComparacion = LASTDATE(DIM_fechas[Fecha])

RETURN

CALCULATE(COUNTROWS(M015_Maestro_de_Empleados),

M015_Maestro_de_Empleados[Fecha de alta en compañía]<=FechaComparacion,

M015_Maestro_de_Empleados[Fecha de bajas]>=FechaComparacion

)

THIS IS THE DATA

Employee	Starting Date	Endig Date	%
18000007	04/01/2019		1
1000060	17/10/2016		0.81
20000002	04/10/2021		1
1000235	01/10/2017		1
1000234	01/10/2017		1
1000230	01/10/2017		1
1000257	01/09/2018		1
1000206	22/05/2017		1
1000231	01/10/2017		1
1000217	24/07/2017		1
16000003	01/09/2008		1
1000312	04/01/2021		1
18000034	10/12/2019		1
1000237	01/10/2017		0.75
1000203	03/04/2017		1
18000023	03/09/2019		1
16000004	16/04/2018		1
18000051	10/02/2021		1
16000001	01/06/1998		1
1000191	21/10/2016		1
1000236	01/10/2017		0.87
1000232	01/10/2017		1
18000060	02/08/2021	27/10/2021	1
1000070	17/10/2016		1
1000225	01/10/2017		1
1000296	01/05/2021		1
1000331	13/12/2021		1
1000158	21/10/2016		1
18000001	01/04/2019		1
1000043	17/10/2016		1
1000116	21/10/2016		1
1000274	23/04/2019		1
1000130	21/10/2016		1
2000010	01/12/2020		1
18000019	18/05/2021		1
1000090	21/10/2016		1
1000137	21/10/2016		1
1000313	04/01/2021		1
1000216	24/07/2017		0.75
9000004	15/02/2016		1
2000003	12/03/1996		1
2000004	01/10/2000		1
1000074	17/10/2016		1
1000251	07/05/2018		1
1000238	01/10/2017		1
18000048	01/07/2020	12/04/2021	1
2000006	06/03/2009		1
1000073	17/10/2016		1
2000008	15/05/2018	15/07/2021	1
1000022	12/02/2015		1
1000067	17/10/2016		1
1000281	01/07/2019	22/04/2021	1

I need to obtain (in days) the FTE per employee and display it in a card, filtering by date (actual or past dates)

I tried to obtain it with a calculated column, but it does not give a variable FTE so it is not vaild...

Any idea/suggestion about by problem??

Thanks!!!

[Greg_Deckler · Accepted solution]

@jbqrn I have an FTE calculation in DAX Cookbook. Chapter 7, Recipe 5 and you can get the DAX here: PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com)

[jbqrn]

thank you @Greg_Deckler for the support and incredible amount of resources!