Calculate Project Resources

[Anonymous]

Hi

I’m having trouble caluclating total resource allocation per month

I have a resoucre table that contains.

1. Project Ref (unique project ID)
2. Role (e.g. Project Manager)
3. Required FTE = Resource Allocation (decimal value per day e.g. 0.1 to max of 1)
4. Start Date = Resource Start Date (DD/MM/YYYY)
5. Finish Date = Resource Finish Date (DD/MM/YYYY)
6. Today's Date (DD/MM/YYYY)
7. Sponsor (Name, Surname)

I'm trying to breakdown the date range into months and show the total resource allocation (sum) per Project Ref from Todays Date until the Finish Date.

Any help appreciated.

Thanks

Glen

[Anonymous]

@Anonymous 

You can have a calendar date. Can be something like :

Measure =calculate(sum([Required FTE], filter(calendar table, [date].[month]=max([date].[month])))

And please share a sample table for more accurate solution.

PaulZheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

[Anonymous]

Project Ref	Role	Required FTE	Start Date	Finish Date	Todays Date	Sponsor
Q0023	PM	0.1	12/08/2019	27/03/2020	31/08/2021	A
Q0023	PM	0.1	30/03/2020	08/06/2020	31/08/2021	A
K4023	PO	1	01/07/2018	30/06/2022	31/08/2021	B
K4038	PD	0.2	16/11/2020	30/09/2022	31/08/2021	C

This is what I have - What I'd like to create is a month by month column that shows the Required FTE for each month between todyas date and the Finish date. e.g.

Project Ref	Role	Required FTE	Start Date	Finish Date	Todays Date	Sponsor	August  2021	September 2021	October 2021	November 2021
Q0023	PM	0.1	12/03/2020	27/08/2021	31/08/2021	A	0.1
Q0023	PM	0.1	30/03/2020	08/09/2021	31/08/2021	A	0.1	0.1
K4023	PO	1	01/04/2020	30/08/2022	31/08/2021	B	1	1	1	1
K4038	PD	0.2	16/11/2020	30/09/2022	31/08/2021	C	0.2	0.2	0.2	0.2

[lbendlin]

As @Anonymous mentioned you need a calendar table. Since your start and end dates do not adhere to month boundaries your calculations need to be on day level. That also means that the values you show in your expected results are not accurate.

Are you planning to consider working days for each month? Do all working days have the same number of hours?

[Anonymous]

Hi

The report just needs to show the total FTE Required per month per Sponsor. So no need for work days or hours.

I do have a date table in the model but am unsure How to make  @V-pazhen-msft suggestion work. 

Thanks Glen