Calculate new employees per month

[HappyBeckmann]

Hey there,

I am trying to create a visual with the new employees for each month. Positions can occur several times but not at the same time. For example Position P-10057.

ValidFrom	ValidTo	FullTimeEquivalent	PositionId
31.12.2023	31.12.2154	0.2	P-10016
01.12.2012	31.12.2154	1	P-10017
18.01.2016	31.12.2154	1	P-10055
01.09.2017	31.12.2154	1	P-10056
01.02.2023	31.12.2023	1	P-10057
30.06.2024	14.10.2024	1	P-10057
14.11.2023	29.02.2024	1	P-10058
31.07.2024	31.12.2154	1	P-10058
01.08.2021	04.06.2024	1	P-10059
01.08.2022	30.07.2025	1	P-10060
01.08.2022	31.07.2025	1	P-10061
15.08.2022	13.08.2025	1	P-10062
01.08.2023	30.07.2026	1	P-10063
01.08.2023	08.05.2024	1	P-10064
01.08.2021	04.06.2024	1	P-10065

I use the following Dax:

FTE_Onboarding =

CALCULATE(

    SUM(PositionWorkerAssignmentsV2[FullTimeEquivalent]),

    FILTER(

        PositionWorkerAssignmentsV2,

        PositionWorkerAssignmentsV2[ValidFrom] >= MIN(DateTable[Date]) &&

        PositionWorkerAssignmentsV2[ValidFrom] <= MAX(DateTable[Date])

    )

)

The datetable goes from 2022 to 2026. So the missing FTE before 2022 are correct.
However I am get the following result:

FTE_Onboarding	Year	Month
3	2022	August
1	2023	Februar
2	2023	August
1	2023	November

So the FTE from Dec 23 and the two FTE from 24 are missing.

Looking forward to your answers 🙂

[DataNinja777]

Hi @HappyBeckmann ,

You can produce your required output by writing the following dax measure.

FTE_Onboarding = 
CALCULATE(
    SUM(PositionWorkerAssignmentsV2[FullTimeEquivalent]),
    FILTER(
        ALL(PositionWorkerAssignmentsV2),  // Removes any existing filters on PositionWorkerAssignmentsV2
        PositionWorkerAssignmentsV2[ValidFrom] >= MIN(DateTable[Date]) &&
        PositionWorkerAssignmentsV2[ValidFrom] <= MAX(DateTable[Date])
    )
)

The resulting output is as shown below:

I have attached an example pbix file for your reference.

Best regards,

[HappyBeckmann]

Thank you for your reply 🙂

The problem seems to be my date table.
I am still trying to figure out why, because it seems to work till Dec 2023

[DataNinja777]

Hi @HappyBeckmann ,

I didn’t mention this earlier, but I found it a bit unusual that your employees have durations exceeding 100 years, as shown below. They certainly have remarkable longevity and tenure!

Best regards,

[HappyBeckmann]

Haha maybe we have found the fountain of youth 😉
Na actually we use F&O and employees with a permanent contract with no end date seem to get the max date of F&O

[AntrikshSharma]

@HappyBeckmann I think there is an issue with your dates table, I haven't even started yet I can see the FTEs for the missing months you have mentioned.

Refer to the file attached below.

[HappyBeckmann]

Thank you for your reply 🙂
I managed to make it work by instead of using the datetable in the visual I am using the ValidFrom.
So you are correct.

My datetable looks like this:

DateTable =

CALENDAR(

    DATE(2022, 1, 1),

    DATE(YEAR(TODAY()) + 2, 12, 31)

)
Data type is date
Format is short date

Valid From has data type date and time

Maybe that's the issue? But why does it work till end of 2023? 

[johnt75]

Try

FTE Onboarding = 
VAR StartDate = MIN('Date'[Date])
VAR EndDate = MAX('Date'[Date])
VAR Result = CALCULATE(
    SUM('Table'[FullTimeEquivalent]),
    'Table'[ValidFrom] >= StartDate
    && 'Table'[ValidFrom] <= EndDate
)
RETURN
    Result

[HappyBeckmann]

Thank you for your reply 🙂
However it seems like the dax is not the problem but the date table

[johnt75]

There's a few things you could try. Firstly check that Power BI hasn't automatically created any relationships between your date table and the fact table.

For the fact table, change the datetime to date. You'll need to do this both in Power Query and in the modelling view. If you only do it in the modelling view it will silently retain the time part.

Make sure that the date table is marked as a date table.

Finally, have a look at the date table and make sure that it does have all the dates you expect. Its very easy to make a typo and end up with 

YEAR( TODAY() + 2)

rather than

YEAR( TODAY() ) + 2