cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

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 RelationshipTable 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 PeriodTotal Employees per Period

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

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.

View solution in original post

27 REPLIES 27

@rl_evans Just to clear it up, the 1753 date gets converted in SQL to blanks, for some reason Navision data directely in the database states it like that, apologise for me lazyness 🙂

 

The reason I suspected that, is because no matter what I try, my yearly stats gets messed up, and I cant figure out why.

 

Changing what you suggested unforntantly sill gave the incorrect results.

@rl_evans

 

I see that you do take the acocunt for termintation dates in the future, correct ?

 

Im asking cause it seem that DAX calculation is exactly excluding all of current active employees with a termination date in the future, and cant figure out why that is.

 

 

 

 

 

 

 

@rado81  - My DAX formula calculates the number of active employees at the end of any given period.  The initial Max date variables captures the max date given the current date context.  Then, the CalculateTable function creates a table of all employees hired on or before the max date. The ALL function causes the CalculateTable to ignore the current date context. I'm doing this because the date context has a min and max date and I need to not only find all employees within the date context but also those hired before the date context; since those may still be active. The Calculate function looks for all active employees found in the intermediate table. Active employees are defined as those whose termination date is either blank or is greater than the max date of the period in context.
Hope this helps.

I have the excat same probles.

please help

Resolver IV
Resolver IV

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.

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.

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 <=

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors