Headcount in time period

[RopJD]

Hi,
I would like to use a splicer to get a Headcount on any chosen day.
I have a MasterDate table with every date populated from 2008-2030.
I have a measure that is working to calculate "TODAY" Headcount.

I need help with a measure to calculate the headcount on any given day.
My data is updated daily which is why today's headcount is always accurate. There is always an "EffectiveDate" for "Today" with the most recent action regardless of how long ago it was. My sample data should show this.

EffectiveDate	EmployeeID	NAME	Employee Status	Action	LastHireDate	Termination Date
2023-07-06	87	Joe Brown	I	Termination	2010-01-01	2010-06-01
2023-07-06	137	John Doe	A	Data Change	2021-11-03
2023-07-06	256	Mary Jane	I	Termination	2023-06-15	2023-07-01
2023-07-01	256	Mary Jane	I	Termination	2023-06-15	2023-07-01
2023-06-15	256	Mary Jane	A	Hire	2023-06-15
2023-04-12	137	John Doe	A	Data Change	2021-11-03
2023-03-30	137	John Doe	A	Pay Increase	2021-11-03
2023-01-05	137	John Doe	A	Data Change	2021-11-03
2022-03-31	137	John Doe	A	Pay Increase	2021-11-03
2022-01-10	137	John Doe	A	New Assignment	2021-11-03
2021-11-03	137	John Doe	A	Rehire	2021-11-03
2021-10-28	137	John Doe	I	Termination	2021-04-26	2021-10-27
2021-04-26	137	John Doe	A	Rehire	2021-04-26
2021-04-24	137	John Doe	I	Termination	2020-10-15	2021-04-23
2021-04-01	137	John Doe	A	Pay Increase	2020-10-15
2020-12-07	137	John Doe	A	Data Change	2020-10-15
2020-12-03	137	John Doe	A	Data Change	2020-10-15
2020-11-17	137	John Doe	A	Data Change	2020-10-15
2020-11-05	137	John Doe	A	New Assignment	2020-10-15
2020-10-15	137	John Doe	A	Rehire	2020-10-15
2020-09-22	137	John Doe	I	Termination	2020-08-04	2020-09-21
2020-08-26	137	John Doe	A	Data Change	2020-08-04
2020-08-04	137	John Doe	A	Rehire	2020-08-04
2020-05-18	137	John Doe	I	Termination	2019-10-17	2020-05-17
2020-04-30	137	John Doe	I	Layoff	2019-10-17	2020-04-29
2020-04-30	137	John Doe	A	Severance	2019-10-17	2020-04-29
2020-04-30	137	John Doe	A	Data Change	2019-10-17	2020-04-29
2020-03-31	137	John Doe	A	Data Change	2019-10-17
2020-03-19	137	John Doe	A	Pay Increase	2019-10-17
2019-10-17	137	John Doe	A	Rehire	2019-10-17
2019-08-31	137	John Doe	I	Termination	2019-05-06	2019-08-30
2019-05-06	137	John Doe	A	Rehire	2019-05-06
2018-09-01	137	John Doe	I	Termination	2018-05-07	2018-08-31
2018-05-07	137	John Doe	A	Rehire	2018-05-07
2017-09-01	137	John Doe	I	Termination	2017-05-01	2017-08-31
2017-05-01	137	John Doe	A	Hire	2017-05-01
2010-06-01	87	Joe Brown	I	Termination	2010-01-01	2010-06-01
2010-06-01	87	Joe Brown	A	Retirement	2010-01-01	2010-06-01
2010-01-01	87	Joe Brown	A	Hire	2010-01-01

My problem with my above code is that John Doe counts as 0 headcount beyond his LastHireDate.
My expected result is to have a headcount on any given day using a splicer from MasterDates.
Today = 1.
2023-06-20 = 2
2021-09-01 = 1
2016-01-01 = 0
Any help is appreciated.

[v-yueyunzh-msft]

Hi, @RopJD 

Thanks for your sample data ! According to your dax , the relationship between two tables seems to this:
'ALL EmpioyDate'[LastHireDate] ---- 'Mater Date'[Date]?

And for your need , sorry for i am not clear about how to get the result from your sample data:

Today = 1.
2023-06-20 = 2
2021-09-01 = 1
2016-01-01 = 0

In your dax , you compare the [LastHireDate] and the [Termination Date] with the Master Date[Date]. But you use the [EffectiveDate]=TODAY() to filter and i do not understand what you want to get for the last.

Can you give me the detailed calculation steps for your result like 2021-09-01 = 1,2023-06-20 = 2 and Today = 1 based on your sample data? Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly