topic Re: FTE per employee is correct but not total in DAX Commands and Tips

FTE per employee is correct but not total

Bokazoit — Fri, 17 Feb 2023 07:27:22 GMT

My measure works fine on a per employee, but the total is wrong. The measure is this:

FTE ny = Var MaxDate = MAX ( DimDato[Dato] ) Var MinDate = MIN ( DimDato[Dato] ) VAR EmpStartDate = MIN ( DimStamdata[Medarbejder startdato] ) VAR EmpEndDate = MAX ( DimStamdata[Medarbejder slutdato] ) VAR MinWorkDate = IF ( EmpStartDate >= MinDate, EmpStartDate, MinDate ) VAR MaxWorkDate = IF ( EmpEndDate <= MaxDate && EmpEndDate <> BLANK() , EmpEndDate, MaxDate ) VAR WorkMthCount = IF ( DATEDIFF(MinWorkDate,MaxWorkDate,MONTH) < 0, BLANK(), DATEDIFF(MinWorkDate,MaxWorkDate,MONTH) + 1) VAR MonthVarCount = DATEDIFF ( MinDate, MaxDate, MONTH ) RETURN ([Arbejdstid] / 37.5) * ( WorkMthCount / (MonthVarCount + 1 ) )

Arbejdstid is working hours per week, and the result:

FTE ny is my measure that correctly calculates the FTE over full year, were employee 9304 has been hired for only 2 month giving it a 0,17 (2 out of 12 month) FTE count. But the Total 3,67 is not correct, it should be (1 + 1 + 0.67 + 0.17 = 2.84)

What am I missing?

Re: FTE per employee is correct but not total

tamerj1 — Fri, 17 Feb 2023 07:40:11 GMT

Hi Bokazoit
Please try

FTE ny = SUMX ( VALUES ( 'Table'[Lonnummer] ), CALCULATE ( VAR MaxDate = MAX ( DimDato[Dato] ) VAR MinDate = MIN ( DimDato[Dato] ) VAR EmpStartDate = MIN ( DimStamdata[Medarbejder startdato] ) VAR EmpEndDate = MAX ( DimStamdata[Medarbejder slutdato] ) VAR MinWorkDate = IF ( EmpStartDate >= MinDate, EmpStartDate, MinDate ) VAR MaxWorkDate = IF ( EmpEndDate <= MaxDate && EmpEndDate <> BLANK (), EmpEndDate, MaxDate ) VAR WorkMthCount = IF ( DATEDIFF ( MinWorkDate, MaxWorkDate, MONTH ) < 0, BLANK (), DATEDIFF ( MinWorkDate, MaxWorkDate, MONTH ) + 1 ) VAR MonthVarCount = DATEDIFF ( MinDate, MaxDate, MONTH ) RETURN ( [Arbejdstid] / 37.5 ) * ( WorkMthCount / ( MonthVarCount + 1 ) ) ) )

Re: FTE per employee is correct but not total

Bokazoit — Fri, 17 Feb 2023 08:53:37 GMT

Thank You, that did it almost. Meaning over year it gave me the correct result, but now the employees working on hourly wage is not correct in the total:

FTE Norm Timeloen = CALCULATE([Arbejds-timer i perioden] / 162.5 , FILTER(DimJob, DimJob[Ansættelsesform] = "Timelønnede"))

The total should be average over the periode per worker

Re: FTE per employee is correct but not total

tamerj1 — Fri, 17 Feb 2023 09:21:35 GMT

Bokazoit

Are we talking about one or two measures? Which one has the problem? By the way, what is the column 'Table'[Lonnummer] and from which table? And the MonthYear from which table?

Re: FTE per employee is correct but not total

Bokazoit — Fri, 17 Feb 2023 09:29:38 GMT

FTE norm timeloen is the measure for employees payed on hourly basis

Lonnummer = employee number and comes from DimStamdata (dimension containing all employer specific data such as name adress etc.) and MonthYear from my data dimension

Re: FTE per employee is correct but not total

tamerj1 — Fri, 17 Feb 2023 09:35:14 GMT

Bokazoit
Please try

FTE Norm Timeloen = SUMX ( CROSSJOIN ( VALUES ( DimStamdata[Lonnummer] ), VALUES ( 'Date'[MonthYear] ) ), CALCULATE ( [Arbejds-timer i perioden] / 162.5, FILTER ( DimJob, DimJob[Ansættelsesform] = "Timelønnede" ) ) )