Forum Discussion
Helper IVForecast Remaining Capacity for Succeeding Years
Hello Everyone. Seeking for your help in my bar chart. I wish to see the remaining capacity left for 12 months of the current year and for the next 12 months of another year.
Here's the outcome I desire to have:
1. I want to see the remaining capacity from Jan 2022 up to Dec 2023. If Dec 2023 ends, it will automatically create a 12 months for 2024.
2. For the bar of each month, I have a problem with my " Available Capcity Left" column because of the overlapping months of two projects. As you can see in my sample data, the month of June 2022 with two projects does not deduct the two assigned capacity. So for the month of June 2022, the bar should be:
Remaing capacity left = 5 days - (2.5 days + 5 days)
= -2.5 days
where:
5 days= maximum capacity
2 days = assigned capacity for Project A until July 30, 2022
5 days= assigned capacity for Project B until Sept 30, 2022
June 2022 should be -2.5 days.
3. Once Project A end in July 30, 2022, the remaining capacity left for the succeding months (i.e from August 2022 to Dec 2023) should be :
Remaing capacity left = 5 days - 5 days
= 0 days
where:
5 days = maximum capacity
5 days= assigned capacity for Project B until Sept 30, 2022
Fig. A- Wrong calculation per month
Fig B: Desired calculation and visualisation
Appreciate your help.
Sample Data:
| Month | Assigned Resource | Assigned Project | Start Date | End Date | Maximum Capacity | Project Capacity Assigned | Available Capacity Left |
| 01/05/2022 | Employee A | Project A | Sunday, 1 May 2022 | Saturday, 30 July 2022 | 5 | 2.5 | 2.5 |
| 01/06/2022 | Employee A | Project A | Sunday, 1 May 2022 | Saturday, 30 July 2022 | 5 | 2.5 | 2.5 |
| 01/06/2022 | Employee A | Project B | Wednesday, 1 June 2022 | Friday, 30 September 2022 | 5 | 5 | 0 |
| 01/07/2022 | Employee A | Project A | Sunday, 1 May 2022 | Saturday, 30 July 2022 | 5 | 2.5 | 2.5 |
| 01/07/2022 | Employee A | Project B | Wednesday, 1 June 2022 | Friday, 30 September 2022 | 5 | 5 | 0 |
| 01/08/2022 | Employee A | Project B | Wednesday, 1 June 2022 | Friday, 30 September 2022 | 5 | 5 | 0 |
| 01/09/2022 | Employee A | Project B | Wednesday, 1 June 2022 | Friday, 30 September 2022 | 5 | 5 | 0 |
7 Replies
- TomMartens
Super User
Hey third_hicana ,
please create a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrive, google drive, or dropbox and share the link.
If you are using Excel to create the sample data instead of using the manual input method, share the Excel file as well.
Regards,
Tom
Hi TomMartens Here are the links. Thank you.
Data:
https://1drv.ms/x/s!AoVsZM9hRj8DgZNxWj1MyVQnL98siQ
Pbi- https://1drv.ms/u/s!AoVsZM9hRj8DgZN2lK6IQ0aS5qDd6Q?e=DE9df0
Regards,Third
- TomMartens
Hey third_hicana ,
I can't follow your explanation. Looking at the data inside the pbix file I do not see the data you are talking about. Please check the data and explain the expected result based on the data you provide in the pbix and Excel. Make sure that both files contain the same data.
I see 4 projects for June 2022, none of these projects is A or B.
Regards,
Tom
Kindly use this data. Basically my goal is to evaluate how many days are left to each employee on a monthly basis based on the capacities required assigned to him/her.
So there is only one maximum capacity per month which is 5 days. If there are two assigned capacities, these should be subtracted only to 5 days in that particular month. The more you assigned capacity days in a particular month, the 5 days maximum continues to decrease.
The month in X axis should be months of the calendar and not from the month column of the assignment table. Employee A has a maximum of 5 days capacity. If there is no project and assigned capacity, it should show 5 days in the bar of the months of no assignment,.
Link:
https://1drv.ms/x/s!AoVsZM9hRj8DgZQyht21Hp9v6vxHlg?e=eFXTVc
As you can see for June and July, it should be -2.5 days. If you subtract the capacities assigned from two projects of Employee A for the month of June and July to 5, it should be negative.
- ImkeF
Community Champion
Hi third_hicana ,
it looks as if you have to calculate the average of the maximum capacity and subtract the sum of the project assigned capacities from it.
A measure like this would do it:RemainingCapacityByMonth = SUMX ( VALUES ( Table2[Month] ), CALCULATE ( AVERAGE ( Table2[Maximum Capacity] ) ) - CALCULATE ( SUM ( Table2[Project Capacity Assigned] ) ) )Make sure to replace the Month-column by a reference to the month column of your calendar table.
Also, you might have to add the employee id to the iterated rows to create the correct result if no employee is selected on the visual.
third_hicanaHi ImkeF I have already ID number for employees. What I need to achieve is a montly check and balance of how many days available left to each employee and not the average. I cannot also subtract the sum of assigned capacities because some of the projects end in different months. So my desire outcome is a monthly check of days left.
- ImkeF
Hi third_hicana ,
sorry, I don't understand your requirement.
So hopefully someone else will pick this up.
