Full time employees average and sum of average fro...

REGHnoob · ‎11-02-2019

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

Anonymous · ‎11-04-2019

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 · ‎11-05-2019

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