Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Employees by Period

I would like to show the demographics of employees on a Diversity, Equity & Inclusion dashboard. In my employees table, I have start/hire and end/termination dates along with the demographic info I need. Additionally, I have a date table that is connected to the employee table with an active connection from hire date and an inactive connection from termination date. 

 

 

 

I am using the below measure to return only the employees active during the period selected in the date slicer, but I'm getting some unexpected/undesirable results. First, if the full date range is selected in the slicer, only employees that are still currently active are displayed. Second, changing the start of the range removes employees that were hired before the selected date, and changing the end date appears to remove the employees that were hired after the end date.

 

 

Employee Count = 
VAR MAX_DATE = MAX ( 'Dates'[Date] )
RETURN
    SUMX (
        'Bamboo Employees',
        IF (
            'Bamboo Employees'[Hire Date] <= MAX_DATE
                && OR ( 'Bamboo Employees'[Termination Date] >= MAX_DATE , ISBLANK( 'Bamboo Employees'[Termination Date]) ),
            1,
            BLANK ()
        )
    )

 

 
The behavior I was trying to achieve would be to show all employees that were active employees during the specified range, regardless of their current employment status. For example, if I set the range to 1/1/2019-12/31/2019, I would expect to see something like 300-400 employees, but I am only seeing the 100 employees hired during that period, not anyone who was already hired prior to 1/1/2019. 
 
A sample of my employee table is below. 

Employee #AgeGenderEthnicityEEO Job CategoryEmployment StatusHire DateTermination DateLength of Service: YearsTenure RangeAge Range
3103624MaleWhiteAdministrative Support WorkersFull-Time07/26/2021 0<1 Year<30
1244038MaleWhiteCraft WorkersFull-Time07/15/2021 0<1 Year30-40
1243937MaleWhiteCraft WorkersFull-Time07/08/2021 0<1 Year30-40
1243863FemaleWhiteLaborers and HelpersFull-Time07/08/2021 0<1 Year60+
1243760MaleWhiteLaborers and HelpersFull-Time07/08/2021 0<1 Year60+
1243624FemaleWhiteLaborers and HelpersFull-Time07/06/2021 0<1 Year<30
1243425MaleWhiteCraft WorkersFull-Time07/06/2021 0<1 Year<30
1243061MaleWhiteCraft WorkersFull-Time06/14/2021 0<1 Year60+
1242637MaleWhiteCraft WorkersFull-Time06/02/2021 0<1 Year30-40
3100623MaleWhiteAdministrative Support WorkersFull-Time06/01/2021 0<1 Year<30
3105350MaleWhiteProfessionalsFull-Time05/20/2021 0<1 Year50-60
3106227MaleWhiteLaborers and HelpersFull-Time05/18/2021 0<1 Year<30
238655MaleWhiteCraft WorkersFull-Time05/17/2021 0<1 Year50-60
3106128MaleWhiteLaborers and HelpersFull-Time05/17/2021 0<1 Year<30
3106052MaleWhiteFirst/Mid Level Officials and ManagersFull-Time05/12/2021 0<1 Year50-60

 

Any help in creating the appropriate measure is greatly appreciated. Until this point, I've never had to use any measures outside of quick measures. Thanks!

Need Help

2 Replies