Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
skasper
Responsive Resident
Responsive Resident

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

30 REPLIES 30

@Anonymous  I guess it depends on what you're trying to count.  If the same person is hired twice in the same year, isn't that the same as hiring two people that year?  Just because it's the same person doesn't change the fact that there were two hirings.  In other words, if you ignore the name of the employee, the events are 1) and employee is hired, 2) an employee is separated, 3) an employee is hired.  That's two hires and one separation that occurred in the year.  That doesn't change the number of employees that were active in any given period.

Anonymous
Not applicable

@rl_evans  I agreed with you, but im also not trying to show how many persons where hired at any given period, but how many where active, and if you have the same person hired twice in a year, in terms of active employees it should only be counted as one, right ? it seems to change the number of active employees on the years basis alot, on month basis it looks better.

 

 

 

 

 

 

 

@Anonymous  Looking at your DAX more closely, one thing that jumps out is that your filter is incorrect:

(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod &&
v_FSASHRBIDATA[TerminationDate] >= StartOfPeriod)

It should look like this:

(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod &&
v_FSASHRBIDATA[TerminationDate] > EndOfPeriod) 

To count all active employees in any given period, you want to include those whose termination date is after the end of the period. 

Btw - A year is also a period, so if the same person was hired twice in that period it's irrelevant.  If that period is still active at the end of the year, there should be only one record showing that individual as active.  So, they'd only be counted once.  If your count is off, it's due to a different issue with your DAX.

Finally, in your data, there is a termination date showing the year 1753.  This will not work with your DAX statement as this means the person was terminated before they were hired.  If that date is being used to indicate the individual is still active, then you need to modify your DAX to look for that specific date (1/1/1753).

Anonymous
Not applicable

@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.

Anonymous
Not applicable

@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.

 

 

 

 

 

 

 

@Anonymous  - 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

cs_skit
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.

skasper
Responsive Resident
Responsive Resident

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.