Hi Everyone. Here’s the 11th installment of the Problem of the Week, but this time we’re going to do things a little differently: instead of giving you a DAX or Power Query issue and a starting-point PBIX, this is more of a general Power BI Desktop design and development challenge. We’ll pose a question, provide a small set of sample data, and see what can be developed from scratch.
(For background on the POTW initiative, check out this post .)
A not-uncommon problem that almost everyone experiences is that a project expands in scope over time, and when one side of the PROJECT MANAGEMENT TRIANGLE changes, at least one of the other sides change as well (e.g., if you increase the scope [increase the hours required to complete the project], the only way to keep the time and cost unchanged is to reduce quality, which is rarely an acceptable answer and one that we will not be considering further here.)
So, for the purposes of this example, we will assume that project quality remains fixed, so therefore any increase in scope (which is denoted by an increase in the hours required to complete the project) will necessitate an increase in time or cost (or both).
As well, the Enterprise DNA Extended Date Table (Power Query M function) should be used.
The Business Constraints
You have multiple projects, and every project has a promised due date, and a penalty fee will be applied if the project is delivered late. For this problem, we are only concerned with the “Infrastructure Upgrade” project.
All existing staff have a wage rate of $50/hour (salary plus company overhead), and work a regular 8-hour workday on each business day.
Work can only be conducted on business days (i.e., not on weekends, not on holidays).
You can add staff to try to finish a project on time, but there is a sunk cost of one week’s service before the new staff member can contribute to a project (so $50/hour * 40 hours = $2,000).
You can add overtime for existing staff to try to finish a project on time, and staff can work up to 12 hours per day, again on business days only.
The Solution Constraints
Your solution should:
- Be configurable to allow the user to connect to the data from any folder, and let the user enter the source folder from the user interface (i.e., without modifying the code in Power Query); the solution should handle cases where the user may or may not include a backslash at the end of a folder name
- Provide the answer for a user-selectable project scope increase, in 5% steps from 5% to 100%
- Provide the answer for all project scope increase percentages, in 5% steps from 5% to 100%
- Load the minimum amount of data
So, the problem is how do you handle project scope increases? You have three options:
- Do nothing (pay late penalty fee, no new staff, no overtime)
- Add staff (no overtime, no late penalty fee)
- Add overtime (no new staff, no late penalty fee)
For each percentage increase in the project scope from 5% to 100%, which option is preferred?
Your task will be to use many aspects of Power BI, including both DAX and Power Query, and a four-pillars approach to provide an answer for the question that is consistent with the teachings on Enterprise DNA; try to provide a the best laid-out and organized solution that you can. We will not be providing a starting-point PBIX, rather it’s up to you to create a new Power BI file from scratch. Along with your PBIX, submit a brief statement of the observations from your 5%-100% “Scenarios” table.
As a final note, strive to implement the Enterprise DNA approach as quickly as possible for you on this problem, and note what tasks remain for you at the 2-hour mark. (One of the takeaways for this Problem of the Week is to identify areas in which you’re quite comfortable as well as those that are candidates for further learning.) This is not a competition, but rather a chance for you to evaluate your learning to date.
Posting Your Solution
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.
To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.
EDNA Problem Solver Stickers
Everyone who successfully completes a Problem of the Week will receive one of these cool Enterprise DNA vinyl laptop stickers:
To receive your sticker, when you submit your solution, just send a copy of your physical mailing address to firstname.lastname@example.org.
Deadline and Eligibility
If you want to submit your entry before the solution becomes public, please do so before 9am ET Wednesday May 12, 2021. Reminder that Problem of the Week is open to everyone – members and non-members. Members should just post your solution directly to this thread in the forum. Non-members, you can send your solution to us at the email address above and we will post it to the forum thread for you.
We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message me at @Greg in this forum thread or at the email address above.
Good luck, and enjoy!!!
P.S.: Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at email@example.com. Thanks!