Headcount Forecasting : Current Month Headcount + Future Months (by Month...in Future)

[MarkH007]

Hi all, 

I have a conundrum which I can't quite solve. 

Context: I am doing a HR Dashboard which includes a section on Headcount Forecasting. I am trying to use the Current Month Headcount + Future Months (by Month...in Future). 

The Current Month Headcount is a straightforward measure based on my Headcount data table. 

The Forecast values are based on a Resource Recruitment table which has estimated onboarding dates which I would like to use as my count of starters per Month. 

So, e.g. Plotting future month forecasts... 

Headcount in December = 400

Resource Recruitment table Forecast = January 5

Result to be plotted in January = 405 

and so on... 

Headcount in January = 405 (from previous calc)

Resource Recruitment table Forecast = February 15

Result to be plotted in February = 420

I will incorporate forecasted leavers into the view once I get the starters sorted. 

The individual numbers I can generate. It is the cumulative values moving into the future which is my stumbling block.  

Any help on how to handle the future forecasting would be greatly appreciated. 

[MarkH007]

For the Current Headcount I require the number of Resources each AP and then forecast the aforementioned Resource Request increases to that total for the full year. 

So, 8 members of staff, plan to recruit a further 11 (based on AP02 Month 2 data), but plotting or having the ability to plot each months cumulative total. 

Jan 8, Feb 10 (8 already +2 new recruits) , Mar 10 (from previous +4 recruits) and so on. 

Ideally providing data / measures to allow for forecasting headcount overtime, including ability to plot number of starters in each period etc. 

Acount Period	Start Date of Month	Resource Name	Team	Region	Resource Type
AP01	01/01/2025	Person 1	Team1	UK	Contractor
AP01	01/01/2025	Person 2	Team1	UK	Permanent
AP01	01/01/2025	Person 3	Team1	USA	Permanent
AP01	01/01/2025	Person 4	Team2	UK	Permanent
AP01	01/01/2025	Person 5	Team2	UK	Permanent
AP01	01/01/2025	Person 6	Team1	UK	Contractor
AP01	01/01/2025	Person 7	Team2	UK	Contractor
AP01	01/01/2025	Person 8	Team1	UK	Permanent
AP02	01/02/2025	Person 1	Team1	UK	Contractor
AP02	01/02/2025	Person 2	Team1	UK	Permanent
AP02	01/02/2025	Person 3	Team1	USA	Permanent
AP02	01/02/2025	Person 4	Team2	UK	Permanent
AP02	01/02/2025	Person 5	Team2	UK	Permanent
AP02	01/02/2025	Person 6	Team1	UK	Contractor
AP02	01/02/2025	Person 7	Team2	UK	Contractor
AP02	01/02/2025	Person 8	Team1	UK	Permanent

[v-mengmli-msft]

Hi @MarkH007 ,

Could you please show the expected output?

Best regards,

Mengmeng Li

[MarkH007]

As I append datasets to facilitate the ability to easily to trending and track back through previous months data the creation of measures has proven difficult. 

I have managed to complete the task using a table driven view, however that doesn't support the use of different slice of the data e.g.Team and Region

Output would enable me to toggle a Date Slicer in my main report and view the current number of estimated starters and when they are due to Onboard (start). As Below 

		Jan	Feb	March	April		May
AP01 View	Starters	0	2	4	2		0
AP02 View	Starters	0	2	4	2		2

Raw data looks similar to this. Note the repetition of the Resource Name each Month due to my appended data approach for trending purposes. 

Acount Period	Start Date of Month	Resource Name	Est. Start Date	Team	Region	Resource Type
AP01	01/01/2025	Person 1	08/03/2025	Team1	UK	Contractor
AP01	01/01/2025	Person 2	02/03/2025	Team1	UK	Permanent
AP01	01/01/2025	Person 3	12/03/2025	Team1	USA	Permanent
AP01	01/01/2025	Person 4	25/02/2025	Team2	UK	Permanent
AP01	01/01/2025	Person 5	10/02/2025	Team2	UK	Permanent
AP01	01/01/2025	Person 6	14/04/2025	Team1	UK	Contractor
AP01	01/01/2025	Person 7	22/03/2025	Team2	UK	Contractor
AP01	01/01/2025	Person 8	10/04/2025	Team1	UK	Permanent
AP02	01/02/2025	Person 1	08/03/2025	Team1	UK	Contractor
AP02	01/02/2025	Person 2	02/03/2025	Team1	UK	Permanent
AP02	01/02/2025	Person 3	12/03/2025	Team1	USA	Permanent
AP02	01/02/2025	Person 4	25/02/2025	Team2	UK	Permanent
AP02	01/02/2025	Person 5	10/02/2025	Team2	UK	Permanent
AP02	01/02/2025	Person 6	14/04/2025	Team1	UK	Contractor
AP02	01/02/2025	Person 7	22/03/2025	Team2	UK	Contractor
AP02	01/02/2025	Person 8	10/04/2025	Team1	UK	Permanent
AP02	01/02/2025	Person 9	14/05/2025	Team1	USA	Permanent
AP02	01/02/2025	Person 10	02/06/2025	Team1	UK	Permanent
AP02	01/02/2025	Person 11	12/05/2025	Team2	UK	Permanent

[v-mengmli-msft]

Hi @MarkH007 ,

Could you please provide sample data and expected output? That will help us provide solutions.

Best regards,

Mengmeng Li

[bhanu_gautam]

@MarkH007 Assuming you have a Headcount table with a column named "CurrentMonthHeadcount" and a Resource Recruitment table with columns "ForecastedMonth" and "StartersCount", you can create a calculated column in the Resource Recruitment table to calculate the future headcount.
The DAX formula for calculating the future headcount would be:

DAX
FutureHeadcount =
VAR CurrentMonthHeadcount = RELATED('Headcount'[CurrentMonthHeadcount])
VAR StartersCount = 'Resource Recruitment'[StartersCount]
RETURN
CurrentMonthHeadcount + StartersCount

To calculate the cumulative headcount for each future month, you can use the following DAX formula:

DAX
CumulativeHeadcount =
CALCULATE(
SUM('Resource Recruitment'[StartersCount]),
FILTER(
'Resource Recruitment',
'Resource Recruitment'[ForecastedMonth] <= MAX('Resource Recruitment'[ForecastedMonth])
)
) + MAX('Headcount'[CurrentMonthHeadcount])

[MarkH007]

Thanks, the StartersCount is based on estimated start dates and a count of those per month into the future. 

So, Dec 400 Resource - forecast for 2025 is as follows

Jan 5   - total 405

Feb 15  - total 420 (using previous total)

March 8 - total 428  (using previous total)

April 12 ... total 440  (using previous total)and so on. 

I may have to create an addtional table to summarise totals which probably will limit any ability to slice and dice the data