Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
third_hicana
Helper III
Helper III

Forecast 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

 

third_hicana_1-1665151834897.png

 

Fig. A- Wrong calculation per month

 

third_hicana_0-1665151025704.png

 

Fig B: Desired calculation and visualisation

 

third_hicana_1-1665214277500.png

 

 

 

Appreciate your help. 

 

Sample Data:

 

 

MonthAssigned ResourceAssigned ProjectStart DateEnd DateMaximum CapacityProject Capacity AssignedAvailable Capacity  Left
01/05/2022Employee AProject ASunday, 1 May 2022Saturday, 30 July 202252.52.5
01/06/2022Employee AProject ASunday, 1 May 2022Saturday, 30 July 202252.52.5
01/06/2022Employee AProject BWednesday, 1 June 2022Friday, 30 September 2022550
01/07/2022Employee AProject ASunday, 1 May 2022Saturday, 30 July 202252.52.5
01/07/2022Employee AProject BWednesday, 1 June 2022Friday, 30 September 2022550
01/08/2022Employee AProject BWednesday, 1 June 2022Friday, 30 September 2022550
01/09/2022Employee AProject BWednesday, 1 June 2022Friday, 30 September 2022550
7 REPLIES 7
TomMartens
Super User
Super User

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.

image.png

I see 4 projects for June 2022, none of these projects is A or B.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens 

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. 

third_learner_2-1665329092959.png

 

third_learner_1-1665322447110.png

 

 

TomMartens
Super User
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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

ImkeF
Super User
Super User

Hi @third_hicana ,
sorry, I don't understand your requirement.
So hopefully someone else will pick this up.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

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.


Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.