Forum Discussion
Calculate Project Resources
Hi
I’m having trouble caluclating total resource allocation per month
I have a resoucre table that contains.
- Project Ref (unique project ID)
- Role (e.g. Project Manager)
- Required FTE = Resource Allocation (decimal value per day e.g. 0.1 to max of 1)
- Start Date = Resource Start Date (DD/MM/YYYY)
- Finish Date = Resource Finish Date (DD/MM/YYYY)
- Today's Date (DD/MM/YYYY)
- 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
5 Replies
Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.
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 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
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.
