Active Employee Data

Question

Hi,&nbsp;I have FTE data posted by assignment start and end date (null if they are still in the business) so have used the Count of Active Employee formula stated below, but i also need to show if someone is on maternity leave their FTE should be zero. Some dummy data below to show an example of the data set we have.&nbsp;Mary Jane | start: 1/5/2015 | end: null | FTE: 1 | Absence: Maternity | Absence start: 1/7/21 | Absence end: 1/7/22 |&nbsp;So ideally the FTE should be 1 from 2015 through to 1/7/21, then zero from 1/7/21 - 1/7/22, then 1 again thereafter. Is there anyway to amend my formula to account for maternity breaks?&nbsp;&nbsp;Count of Active Employee = VAR currentDate =&nbsp; &nbsp; MAX ( 'Date'[Date] )RETURN&nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; SUM(hr_costcentre_fte[FTE]),&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; hr_costcentre_fte,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ( hr_costcentre_fte[AssignmentStartDate] &lt;= currentDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; OR(hr_costcentre_fte[AssignmentEndDate] &gt;= currentDate, ISBLANK( hr_costcentre_fte[AssignmentEndDate] &nbsp;))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )))&nbsp;&nbsp;Thanks!

amitchandak · Answer

LouiseB1409 , refer if this can help 
&nbsp;
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4 https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
&nbsp;

Forum Discussion

Active Employee Data

1 Reply

Related content

Counting Active employees per month

Employee retention data

Active Employees per Period

Copy Data activity - ErrorCode=FailedToUpsertDataIntoDeltaTable

Calculating Active Employees by specific Date