cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skasper Member
Member

Active Employees per Period

Hi,

 

struggling with what should be simple. I have two tables ('crm_employees' and 'DimDates'). There exists a relationship between them (see picture below).

table relations.jpgTable Relationship

What I am trying to calculate is

 

  1. the running total of people having started in a certain period, and
  2. the running total of still active people (i.e. 'Contract End Date' empty or in the future)

This is my Measure for the active ones:

Active Employees = 
CALCULATE(
	COUNTA(crm_employees[Employee]);
	crm_employees[Status Code] = "Active"
)

And this for the running total:

Active Employees running total in Date = 
CALCULATE(
	'Key Measures'[Active Employees];
	FILTER(
		ALLSELECTED('DimDates'[Date]);
		ISONORAFTER('DimDates'[Date]; MAX('DimDates'[Date]); DESC)
	)
)

My problems:

  1. is that it does not count employees where the contract start date is empty. I suppose that's because it cannot relate the empty 'contract start' with the 'DimDates' Date field.
  2. the total is only increasing, which is not correct. e.g. we had more employees in august 2016 than in june 2017, but this is not reflected as you can see below. The measure obviously applies the 'active' filter regardless of when an employee became inactive ('contract end date'). I think I need to redefine the measure for counting 'active' employees to consider the period during which they were active (from 'contract start date' to 'contract end date'), but don't know how to go about it.

total employees per month.jpgTotal Employees per Period

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
1 ACCEPTED SOLUTION

Accepted Solutions
skasper Member
Member

Re: Active Employees per Period

Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).

 

As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.

 

Clipboard02.jpg

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
7 REPLIES 7
cs_skit Member
Member

Re: Active Employees per Period

I don't think you can use the Active Status here as that one probably only works for the current Employees as of today right?

 

If you want historic "Active Employees" in a past month they need to have >= Contract Start and <= Contract End within that month.

skasper Member
Member

Re: Active Employees per Period

Thank you. Yes, I think so, too and tried to do so. But so far without success. Any proposal, how the DAX would look like for this, would be much appreciated.

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
cs_skit Member
Member

Re: Active Employees per Period

Not sure how this can be solved just by DAX.

I suggest you do a second Date Table joined on Contract End then you have Startdate and Enddate and then you can filter EndMonth <=

Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Active Employees per Period

@skasper,

Please use the DAX below to calculate Active Employees and check if you get expected result.

Active Employees = 
CALCULATE(
	COUNTA('crm_employees'[Employee]);
	FILTER('crm_employees' , ('crm_employees'[Contract Start] <= LASTDATE('DimDates'[Date]) 
&& 'crm_employees'[Contract End]>= FIRSTDATE('DimDates'[Date])))

)



Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
skasper Member
Member

Re: Active Employees per Period

Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).

 

As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.

 

Clipboard02.jpg

Best - Sascha
Please always mark accepted solutions. It helps others with similar questions or problems. Thank you.
ILAN102010SH Frequent Visitor
Frequent Visitor

Re: Active Employees per Period

I have the excat same probles.

please help

Manisha New Member
New Member

Re: Active Employees per Period

Hi Sascha,

 

I like your apporach. I am new to Power BI - and am looking to sort Joiners, Leavers and Active Running total by month, quarter and YTD. Our fiscal year is July-June. Appreciate your help here.

 

Thanks.