FTE per employee is correct but not total

[Bokazoit]

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?

[tamerj1 · Accepted solution]

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 ) )
    )
)

[Bokazoit]

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

[tamerj1]

@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?

[Bokazoit]

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

[tamerj1]

@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" )
    )
)