Forum Discussion

ritendersingh2's avatar
ritendersingh2
Regular Visitor
2 years ago
Solved

Need help in calculating headcount

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

 

 

Need Help

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    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's avatar
      ritendersingh2
      Regular Visitor
      2 years ago

      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..


      DateActual HCFuture StartsFuture EndingsFuture HCCalulation
      Dec-232136  2136 
      Jan-242019  2019 
      Feb-241964  1964 
      Mar-241672  1672 
      Apr-24 2131381747=E5+C6-D6
      May-24 252471525=E6+C7-D7
      Jun-24  3131212=E7+C8-D8
      Jul-24  1571055=E8+C9-D9
      Aug-24     
      Sep-24     
      Oct-24     
      Nov-24     
      Dec-24