Forum Discussion
Calculate Previous Headcount
Hi Experts, I have the summarized table below and want to create another column to show the previous headcounts. So for example, I want the July 2009 PreviousHeadcounts to start at 0 since there is no previous headcount before it. This step should also take cognizance of the Personnel Area and display its previous count respectively. So in effect, I want the Aug 2009 figures to pick the total employees from July 2009 as the previous count, Sep 2009 to read Aug 2009, and so on. This should also look at the Personnel Area and pick its corresponding headcount. So in this case, If July 2009 for Tarkw is 1932, then July 2009 should be 0, and Aug 2009 should pick the 1932 etc. The Personnel Area and the Period is Key in determining the previous headcount.
Your quick assistance will be helpful to me. Thank you.
3 Replies
- vanessafvg
Community Champion
can you share your data in text format?
Hi Vannesa, here is the data. This is also the calculation I did to get this table:
Monthly_Headcount = SUMMARIZE(Employees,Employees[Period],Employees[Personnel Area],"Total Employees",count(Employees[Personnel Number]))Below is the data and my desired outcome:Period Total Employees Personnel Area PreviousHeadCount Jul-2009 822 Damang 0 Jul-2009 1932 Tarkwa 0 Aug-2009 472 Damang 822 Aug-2009 1934 Tarkwa 1932 Sep-2009 438 Damang 472 Sep-2009 1995 Tarkwa 1934 Oct-2009 452 Damang 438 Oct-2009 2006 Tarkwa 1995 Nov-2009 475 Damang 452 Nov-2009 2081 Tarkwa 2006 Dec-2009 448 Damang 475 Dec-2009 2089 Tarkwa 2081 Jan-2010 510 Damang 448 Jan-2010 2097 Tarkwa 2089 Feb-2010 464 Damang 510 Feb-2010 2091 Tarkwa 2097 Mar-2010 440 Damang 464 Mar-2010 2090 Tarkwa 2091 Apr-2010 468 Damang 440 Apr-2010 2090 Tarkwa 2090
