Forum Discussion
Workload Projection
KHarrisonSJCA Attached a quick-and-dirty Excel formula based solution. No need for PQ here, I believe, though I recommend you to learn it as it will be a life saver in many other situations.
Since you say the tool should provide a 'rough idea' only, why make things more complicated than needed. The example looks 12 months ahead from a given date. If you want to expand it to the full date spectrum of all projects, I trust you can adjust the named date ranges used yourself.
NikolinoDE ,
Thank you so much for your reply to get me started. I didn't get too far, however. When I added the custom colum and used the List.Dates formula, I received "Error" in those cells. See the attached screenshot and maybe you can see where I am getting tripped up.
(Forgive me as the Power Query Editor is a totally new tool for me!)
That's because AI has it wrong, as most of the time when it comes to more complex Excel related issues!!
Change this:
List.Dates([Start Date], Duration.From([End Date] - [Start Date]), #duration(1, 0, 0, 0))
To this:
List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1, 0, 0, 0))
and the error will go away. However, I doubt that the rest of instructions will do you any good as they are incomplete and/or inaccurate.
In order to resolve your question, can you clarify if the example data in your screenshot is anywhere near reality? I ask because the duration in months and average monthly hours seem to be random numbers and not related to start and end dates or total hours. Important to know how you would do the scheduling manually in real life.
For the first item, for example, with a start on Jan 5, 2022 and ending on Dec 31, 2022. That's between 11 and 12 months (not 30). Do you want to divide the total hours (128) equally over 11 or 12 months, or proportionally based on the number of (working) days in a month? Or perhaps based on another method.
Always good to give a complete picture from the start. That saves a lot of guess work.
Thanks for the quick reply as well.
To your point of the project duration column, that was on me. I was using the formula "=(DAYS(C2,B2))/12" when I should have been using "=(DAYS(C2,B2))/30" to give the approximate duration in months. (Approximate due to using 30 days for each month.)
Given that this tool is intended to provide a rough idea of what we can expect in future months based off bookings, I know the reality of scheduling will differ. For simplicity I was going to divide the number of hours equally through the date range, which gives me that 11-12 month duration like you said. If we can make it more accurate to reflect the working days available in each month, that would be great, but not worth overcomplicating it.
Hope this helps! Feel free to ask more questions as needed.
KHarrisonSJCA Attached a quick-and-dirty Excel formula based solution. No need for PQ here, I believe, though I recommend you to learn it as it will be a life saver in many other situations.
Since you say the tool should provide a 'rough idea' only, why make things more complicated than needed. The example looks 12 months ahead from a given date. If you want to expand it to the full date spectrum of all projects, I trust you can adjust the named date ranges used yourself.
- With some expected modifications, this gets what I need. Thank you so much for your help, Riny!