Employees by Period

[Anonymous]

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 #	Age	Gender	Ethnicity	EEO Job Category	Employment Status	Hire Date	Termination Date	Length of Service: Years	Tenure Range	Age Range
31036	24	Male	White	Administrative Support Workers	Full-Time	07/26/2021		0	<1 Year	<30
12440	38	Male	White	Craft Workers	Full-Time	07/15/2021		0	<1 Year	30-40
12439	37	Male	White	Craft Workers	Full-Time	07/08/2021		0	<1 Year	30-40
12438	63	Female	White	Laborers and Helpers	Full-Time	07/08/2021		0	<1 Year	60+
12437	60	Male	White	Laborers and Helpers	Full-Time	07/08/2021		0	<1 Year	60+
12436	24	Female	White	Laborers and Helpers	Full-Time	07/06/2021		0	<1 Year	<30
12434	25	Male	White	Craft Workers	Full-Time	07/06/2021		0	<1 Year	<30
12430	61	Male	White	Craft Workers	Full-Time	06/14/2021		0	<1 Year	60+
12426	37	Male	White	Craft Workers	Full-Time	06/02/2021		0	<1 Year	30-40
31006	23	Male	White	Administrative Support Workers	Full-Time	06/01/2021		0	<1 Year	<30
31053	50	Male	White	Professionals	Full-Time	05/20/2021		0	<1 Year	50-60
31062	27	Male	White	Laborers and Helpers	Full-Time	05/18/2021		0	<1 Year	<30
2386	55	Male	White	Craft Workers	Full-Time	05/17/2021		0	<1 Year	50-60
31061	28	Male	White	Laborers and Helpers	Full-Time	05/17/2021		0	<1 Year	<30
31060	52	Male	White	First/Mid Level Officials and Managers	Full-Time	05/12/2021		0	<1 Year	50-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!

[Greg_Deckler · Accepted solution]

@Anonymous 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[Anonymous]

Thank you! I know I tried these before, but I must have done something wrong the first time. The first one worked perfectly for my application.