Headcount per month

[Dimitris_Kats]

Hello members,

I need your help to calculate future headcount based on monthly planning for next year.

I have a table per position and employee ID with the headcount at the end of 2021 and the new hires per month for 2022

Dividion	Position	Employee_ID	Headcount 2021	month	New Hires
IT	Web Adminitrator	123456	1	1/1/2022	1
IT	Web Adminitrator	123456	1	1/2/2022	0
IT	Web Adminitrator	123456	1	1/3/2022	3
IT	Web Adminitrator	123456	1	1/4/2022	2
IT	Web Adminitrator	123456	1	1/5/2022	0
IT	Web Adminitrator	123456	1	1/6/2022	0
IT	Web Adminitrator	123456	1	1/7/2022	0
IT	Web Adminitrator	123456	1	1/8/2022	0
IT	Web Adminitrator	123456	1	1/9/2022	0
IT	Web Adminitrator	123456	1	1/10/2022	0
IT	Web Adminitrator	123456	1	1/11/2022	0
IT	Web  Adminitrator	123456	1	1/12/2022	0
FINANCE	Accountant	123457	3	1/1/2022	-1
FINANCE	Accountant	123457	3	1/2/2022	0
FINANCE	Accountant	123457	3	1/3/2022	0
FINANCE	Accountant	123457	3	1/4/2022	0
FINANCE	Accountant	123457	3	1/5/2022	2
FINANCE	Accountant	123457	3	1/6/2022	0
FINANCE	Accountant	123457	3	1/7/2022	0
FINANCE	Accountant	123457	3	1/8/2022	0
FINANCE	Accountant	123457	3	1/9/2022	0
FINANCE	Accountant	123457	3	1/10/2022	0
FINANCE	Accountant	123457	3	1/11/2022	0
FINANCE	Accountant	123457	3	1/12/2022	0

I need to add to the 2021 headcount the every month new hirings.

The result i am seeking is:

	1/1/2022	1/2/2022	1/3/2022	1/4/2022	1/5/2022	1/6/2022	1/7/2022	1/8/2022	1/9/2022	1/10/2022	1/11/2022	1/12/2022
Web Adminitrator	2	2	5	7	7	7	7	7	7	7	7	7
Accountant	2	2	2	2	4	4	4	4	4	4	4	4

 Do you think thats possible?

Thank you very much in advance!

[Dimitris_Kats · Accepted solution]

I found the dax i was looking for. I am writting it here in case someone needs it 🙂

CALCULATE(SUMX('table', 'table'[new hires]),ALLEXCEPT('table','table'[Employee_ID]), 'table'[Month] <= EARLIER('table'[Month]))

[Dimitris_Kats]

So i have manage to achive what I want only for 1 month. I need cumulative to achive it for all months.

This is the calculated column i created: 

Cumulative Headcount 2022 = if('table'[Month] = date(2022,01,01), 'table'[2021 Total]+ 'table'[New Hires], 'table'[2021 Total]+ 'table'[New Hires] + CALCULATE(SUM( 'table'[New Hires]), allexcept('table','Headcount - Total & Monthly'[Employee_ID]),  PREVIOUSMONTH('table'[Month])))

This measure works ok only for the second month for the year. I need at the third month to add the previous 2 months etc and i have no idea how to achive it. 😞

[amitchandak]

@Dimitris_Kats , Hope you are using a date table for this one. So for head count 2021 , you remove join with date table using cross filter or create a measure like

calculate([Head count], filter(All('Date') , 'date'[Year] =2021))

You can add this to your 2022 measure

[Dimitris_Kats]

Hello and thank you very much for your reply.

I am not using a date table. I have the headcount of 31/12/21 in this table and I have also dates in this table for 2022 new hires.
I don't need the 2021 headcount, I already have it. I need to cumulative calculate the headcount per month for 2022 adding the new hirings.

Thank you in advance