Active Employee Data

[LouiseB1409]

Hi,

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.

Mary Jane | start: 1/5/2015 | end: null | FTE: 1 | Absence: Maternity | Absence start: 1/7/21 | Absence end: 1/7/22 |

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? 

Count of Active Employee =

VAR currentDate =

    MAX ( 'Date'[Date] )

RETURN

    CALCULATE (

        SUM(hr_costcentre_fte[FTE]),

        FILTER (

            hr_costcentre_fte,

            ( hr_costcentre_fte[AssignmentStartDate] <= currentDate

              && OR(hr_costcentre_fte[AssignmentEndDate] >= currentDate, ISBLANK( hr_costcentre_fte[AssignmentEndDate]  ))

            )))

Thanks!

[amitchandak]

@LouiseB1409 , refer if this can help

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