Full time employees average and sum of average from start and end date

[REGHnoob]

Hi all

• I need to find the aggregated number of full time employees (FTE) at a given month and aggregated per year.
  • For instance if there are 35 with decimal 1 and 3 with decimal 0,5 = 36,5 FTE in jan
  • 35 with decimal 1 and 5 with decimal 0,4 = 37,4 FTE in feb
  • 30 with decimal 1 and 12 with decimal 0,5 =  36 FTE in march
  • the ytd would be 36,3. FTE
• So a sum by month and the average of that sum by year.

Seems so simple but alas. I have given it my best but have come up short. Hope someone out there can help me find the solution.

Obviously there are more departments and I do have subcategories related the position of each employee. I have lefter those out for now as the key is to get the total amount per month and per year.

I do have a dim_calendar but don't see how I can connect it to the list below - do I connect to start or end date.

Year	Department	Personel_ID	START	END	Decimal hired 1 = full time
2019	TEST	254529	01-05-2018 00:00	31-01-2019 00:00	1
2019	TEST	254529	01-02-2019 00:00	31-12-2019 00:00	1
2019	TEST	255893	01-06-2018 00:00	31-12-2019 00:00	0,86
2019	TEST	262295	01-07-2018 00:00	31-12-2019 00:00	0,86
2019	TEST	268026	01-03-2018 00:00	31-10-2018 00:00	0,65
2019	TEST	268026	01-11-2018 00:00	30-11-2019 00:00	1
2019	TEST	268026	01-12-2019 00:00	31-12-2019 00:00	0,86
2019	TEST	270941	01-07-2012 00:00	31-12-201900:00	0,86
2019	TEST	271524	01-03-2018 00:00	31-08-2019 00:00	1
2019	TEST	271524	01-09-2019 00:00	31-12-2019 00:00	0,86

One of the solution I have tried is the one below. It appeared to work but I know for sure it doesn't handle the total by year correctly as it seems it sums all the personal who has been active during the year. For instance - monthly count could be 37, 38, 41 and yearly sum would be 42 because of the number of employees that started and terminated their contract during the year.

FTE= var maxdate=

MAX(D_Calendar[Date])

return

CALCULATE(SUM(F_Personel_list[decimal hired]);

FILTER(F_Personel_list;F_Personel_list[START]<=maxdate

&& F_Personel_list[End date] >=maxdate

&& F_Personel_list[decimal hired]>0

)

)

Got it from here: (https://community.powerbi.com/t5/Desktop/Calculating-a-monthly-employee-count-from-a-start-and-end-d...)

I have also tried som groupby but havent had any luck. 

Help would save my bacon. Thx in advance

 BR

Kasper

[v-jayw-msft]

Hi @REGHnoob ,

If I understand you correctly, you should be able to get the yearly sum by creating a measure as below:

FTE_year =

VAR maxdate =

    MAX ( 'D_Calendar'[Date] )

VAR mindate =

    MIN ( D_Calendar[Date] )

RETURN

    CALCULATE (

        SUM ( F_Personel_list[Decimal hired] ),

        FILTER (

            F_Personel_list,

            F_Personel_list[START] <= mindate

                && F_Personel_list[END] >= maxdate

                && F_Personel_list[Decimal hired] > 0

        )

    )

The result would be shown as below:

Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[REGHnoob]

First off - Thank you very much for the effort.

Havent tested yet as I have been swamped. Been busy with work and a sick kid. Just finished for today and Its way past midnight now  - I am knackered beond belief.

Will try this out asap and get with hopefully a solved and a big thanks.

br

Kasper

[v-jayw-msft]

Hi @REGHnoob ,

Did the problem solve?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[REGHnoob]

Hi  v-jayw-msft 

Darn it. have to change my original answer to from yes to no. It actually haven't.

In a matrix the total doesn't sum correctly.