Resource Management

[BKWInUK]

EDIT:- To make it more clear of what I am looking for. 

I am trying to put a resource demand schedule together and need some help. 

I have 2 inputs files

1 is the resources I have available this is called Resource Facts, this shows me by week, Region and Role how many resources I have and what hours they can work each week. 

In this example, I can see I have 2 Field Engineers Advanced available in NALA but 0 in EMEA 

Weekstarting	Region	Role	Number of Roles	Target Hours per Week
03/02/2020	NALA	Field Engineer Advanced	2	80
10/02/2020	NALA	Field Engineer Advanced	2	80
17/02/2020	NALA	Field Engineer Advanced	2	80
24/02/2020	NALA	Field Engineer Advanced	2	80
02/03/2020	NALA	Field Engineer Advanced	2	80
09/03/2020	NALA	Field Engineer Advanced	2	80
16/03/2020	NALA	Field Engineer Advanced	2	80
23/03/2020	NALA	Field Engineer Advanced	2	80
30/03/2020	NALA	Field Engineer Advanced	2	80
03/02/2020	EMEA	Field Engineer Advanced	0	0
10/02/2020	EMEA	Field Engineer Advanced	0	0

2nd Input is the Resource Demand file 

This tells me what the projects need that are coming up by the start date and End Date of the requirement and by Region Forecast and Backlog. 

In this example, I can see I need 40 hours for a Business Analyst in NALA for 6 weeks at 40 per weeks or 240 hours for the total amount. 

Start Date	End Date	Role	Region	Forecast HPW	Backlog HPW
03/02/2020 00:00	08/03/2020 12:00	Business Analyst	NALA	40	0
03/02/2020 00:00	05/02/2020 03:00	Learning Specialist	EMEA	1	1
03/02/2020 00:00	06/02/2020 00:00	Learning Specialist	EMEA	8	0
03/02/2020 00:00	07/02/2020 00:00	Learning Specialist	EMEA	16	0
03/02/2020 00:00	07/02/2020 19:30	Learning Specialist	EMEA	22.5	0

In excel I think I can join the 2 tables with a SUMIFS based on region, Role and if the start and end dates are between the Week starting on the Resource Facts table. 

Using the example of the business Anyalist - I am looking to see if I have enough business Ayylalists to cover the 6 weeks of hours required. 

An Example of what I am looking for:-

This is the ideal end result, where I can see by Region Role and date, how many resources I have (number of Roles), The hours those resource can work (Target Hours per Week). Then look at the Resource demands come in for what we call Backlog (Backlog hours) and Forecast (Forecast Hours). (Note- Backlog is what is secured work, Forecast is non secured work that may come up in the future) I can then add those figures together to give me the total required demand hours (Total Required Hours) and then calculate if I have enough resource to meet the demand (Capacity Hours Remaining)  

[BKWInUK]

Link to example Excel and PowerBI

https://1drv.ms/u/s!Aq_hmfWvbjj9gbZgV_Sg-0EFc-sjBw?e=4t8Q44

[v-frfei-msft]

Hi @BKWInUK ,

Sorry I cannot get you. Could you please share your excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[BKWInUK]

hi did the update help?