Need help in calculating headcount

[ritendersingh2]

Hey There ,

I need some help to calculate the future Headcount based on the actual headcount ,future joiners and furture endings ..

So, I have 3 table , Actual Headcount till Mar , Future starts and Furture ends ..

now I need to predict Headcount for future , 

for April - Mar HC + April Joiners- April Endings and that will become my HC for April

for May - April HC +May Joiners- Mat Endings

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[ritendersingh2]

Thank you @Ashish_Mathur for your response,

I assume this will work

Date	Actual HC	Future Starts	Future Endings	Future HC	Calulation
Dec-23	2136			2136
Jan-24	2019			2019
Feb-24	1964			1964
Mar-24	1672			1672
Apr-24		213	138	1747	=E5+C6-D6
May-24		25	247	1525	=E6+C7-D7
Jun-24			313	1212	=E7+C8-D8
Jul-24			157	1055	=E8+C9-D9
Aug-24
Sep-24
Oct-24
Nov-24
Dec-24

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached files.  Please review my solution and adapt it to your needs.

[ritendersingh2]

Thanks @Ashish_Mathur  ,It worked .

[PhilipTreacy]

Hi @ritendersingh2 

Download example PBIX file

You haven't provided any data so I'm guessing how your tables look and the data contained in them.  But using the sample data I created in the PBIX file linked above, you can get what you want with these measures

Prev HC = CALCULATE(SUM('Actual Headcount'[Actual HC]), PREVIOUSMONTH('Actual Headcount'[Date]))

Future HC = [Prev HC] + SUM('Future Starts'[Future Starts]) - SUM('Future Endings'[Future Endings])

Regards

Phil

[ritendersingh2]

Thank you for your response @PhilipTreacy 

I should have explained the situtaion in details ;

So when we are calulating headcount for May, calculation should be Aprils new new headcount(1743 in this case)+ May's Joiners- May's endings..

Date	Actual HC	Future Starts	Future Endings	Future HC	Calulation
Dec-23	2136			2136
Jan-24	2019			2019
Feb-24	1964			1964
Mar-24	1672			1672
Apr-24		213	138	1747	=E5+C6-D6
May-24		25	247	1525	=E6+C7-D7
Jun-24			313	1212	=E7+C8-D8
Jul-24			157	1055	=E8+C9-D9
Aug-24
Sep-24
Oct-24
Nov-24
Dec-24