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
Helper II
Helper II

Hi,

 

I was trying to do something similar, but for job requisitions. Wanting to count the number of open requisitions over time. My "Start date" equivalent is "Approved date" and my "End date" equivalent is "Last modified date".

 

The below formula worked for me:

 

Requisitions Open = 
VAR MinDate =
    MIN ( 'Date Table'[Date] )
VAR MaxDate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT (Requisitions[Job Req ID] ),
        Requisitions[Approved Date] <= MinDate,
        Requisitions[Last Modified] >= MaxDate
    )

This is my output:

 

Capture2.PNG

 

I have a simple data model:

 

Capture.PNG

 

Happy to provide more detail if that helps.

 

Thanks,

 

Matt

mtomlinson - The difference with your formula is that it won't include the requisitions that opened prior to the minimun date and are still open.  This may or may not be relevant in your solution.  When counting all active employees within a specified date range, those with a hired date before the date range (and are still active) need to be included.

@rl_evans Actually, I don't see why that would matter - if an employee had a date before the minDate, it would still meet the criteria to be counted...

 

Am I missing something?

@rl_evans also, as an aside - how have you defined your data model? Are you using inactive relationships? If so, have you be able to keep ineractions working so that you can, for example, select a particular month and view all employees active for that particular month?

@mtomlinson - I have an active relationship between the date table and the employee table with a one to many relationship between date[date] and employee[hiredate].  

Each bar in the bar chart (using your example) represents a given period (i.e., date range).  This date range sets the date context for the DAX statement.  The date context filters the employee table to only those employees hired within the date range context (because of the relationship with the date table).  Think of it as if the DAX statement is getting executed separately for each period.  When looking for all active employees at the end of the period, all employees hired before the date range context have to be included as they may still be active employees.  The ALL() function allows the CalculateTable to ignore the period's date range context.

I tried using your DAX statement and found (in my model) that it doesn't include employees hired prior to the period.  Have you verified that your formula includes all requisitions opened prior to the period and are still open?

 @mtomlinson - I did a little more digging.  Since you don't have an active relationship between date and requisition, then you don't have a date context that limits records in your calculate statement.  So, if you modified your DAX statement to look like the below, it should behave the same as mine.  This assumes the Last Modified date is blank if the requisition is still open.

Requisitions Open = 
VAR MaxDate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT (Requisitions[Job Req ID] ),
        Requisitions[Approved Date] <= MaxDate,
        OR(ISBLANK(Requisitions[Last Modified]),
Requisitions[Last Modified] > MaxDate) )

 

@rl_evans Thanks for digging into this - it's a trickier one to reoslve than I first thought!

I've tried the formula you suggested, but this still doesn't allow me to filter using the chart unfortunately.

I've also tried replicating what you have by removing my relationship with [Last Modified Date] and the [Date] in my Date Table, then making the relationship between [Approved Date] and [Date] active. I've then used your original formula. The chart looks fine, but when I seect a month in the chart to filter by, all my rows come up blank (see screenshot below).

Are you able to successfully filter for the relevant employees active in the period using you chart?

 

Capture.PNG

@rl_evans I assume the filtering not working is due to the fact that if I'm selecting a particular month, and the relationship between [Approved Date] and [Date] is active, then I'm just filtering the data for all [Approved Dates] in that month. The filter doesn't take into account the measure at all.

@rl_evans I've done a bit of testing and this does seem to be the case

@mtomlinson - That goes back to any visual that is counting active requisitions needs to be based on a calculation that can ignore the date context (when there is an active relationship between date and ApprovedDate.

@rl_evans it's a good point. In my case, all requisitions have an approved date, and my date table is dynamically defined using the minimum approved date.

 

Would a simple OR clause not solve this though? Eg...

 

Requisitions Open = 
VAR MinDate =
    MIN ( 'Date Table'[Date] )
VAR MaxDate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT (Requisitions[Job Req ID] ),
        OR(Requisitions[Approved Date] <= MinDate,ISBLANK(Requisitions[Approved Date])),
        Requisitions[Last Modified] >= MaxDate
    )

Ignore the above - just realised you said a date before the min date, not a blank date!

Microsoft
Microsoft

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

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

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.

I encountered this same requirement this week.  Here is how I solved it.

# Employees At End of Period =
VAR MaxDate = MAX ( 'Date'[Date] )

VAR EmpCnt =
    CALCULATE (
        COUNTROWS (
            CALCULATETABLE ( 'Employees', 'Employees'[HireDate] <= MaxDate, ALL ( 'Date' ) )

                                ),
                              (ISBLANK ( 'Employees'[TerminationDate] ) || 'Employees'[TerminationDate] > MaxDate)
                       )
RETURN
IF ( ISBLANK ( EmpCnt ), 0, EmpCnt )

 

In my data set, employees that are currently active have a blank termination date.  The Calculate function returns Blank, instead of zero, when the count is zero for any given period; which is why the last IF is used after the RETURN.

Hi @rl_evans 

 

So I ended up with this DAX calculation with some help,

 

Count of active employees = 

VAR EndOfPeriod = MAX ('Calendar'[Date])
VAR StartOfPeriod = MIN ('Calendar'[Date])

RETURN
CALCULATE (
DISTINCTCOUNT(v_FSASHRBIDATA[EmpId]),
FILTER(
ALL('v_FSASHRBIDATA'), 
(v_FSASHRBIDATA[EmploymentDate] <= EndOfPeriod &&
v_FSASHRBIDATA[TerminationDate] >= StartOfPeriod)
)
)

+
CALCULATE(
DISTINCTCOUNT('v_FSASHRBIDATA'[EmpId]),
FILTER(
ALL(v_FSASHRBIDATA),
('v_FSASHRBIDATA'[EmploymentDate]<=EndOfPeriod
&& 'v_FSASHRBIDATA'[TerminationDate] = blank()
)
)) 
 
But if an employee has 2 sets of hire date on the same year, it seems to get counted as dobbel.
 
ex.
Capture.JPG
 
The last employee should only be counted as 1 if year is filtered to 2015.
 
Can I somehow take that into account ?
 
Any help/advise would be appreciated.
 
Thanks.

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

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

 

 

 

 

 

 

 

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

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