Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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.
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
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |