Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 ) )
)
)
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 ) )
)
)
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
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?
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
@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" )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |