Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to figure out how to spread the requested hours for a project over the length of that project with a known start and end date.
Example, I have Project 001, it is 600 hours, and it is a six-month project that is due to release June 2020.
In this example, Project 001 would add 100 hours to each month from January to June. If Project 002 had 300 hours with the same length and release date, now each month would have 150 hours.
The end goal is to get a forecast of how many hours we expect in each month for all the projects we have to determine the overall capacity demands for the month. So we'd have something like a bar chart that shows the total hours demand for each month based on the projects that will impact that month.
Notes:
1. I have already created a separate date table called "Calendar" with this column
Calendar = CALENDAR(MIN(report[Estimated Work Start Date]),MAX(report[Estimated Work End Date]))
2. Each project can have multiple requested hours (that’s why the same project is listed multiple times)
3. I cannot share the data it is sensitive but I have created a sample set below
ANY help is VERY appreciated!!
Project | Requested Hours | Estimated Work Start Date | Estimated Work End Date | Number of Days | HoursPerDay | ||||
Project 1 | 4 | Wednesday, December 16, 2020 | Wednesday, December 23, 2020 | 8 | 0.5 | ||||
Project 1 | 20 | Wednesday, December 09, 2020 | Wednesday, December 23, 2020 | 15 | 1.33 | ||||
Project 2 | 12 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 0.14 | ||||
Project 2 | 50 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 0.6 | ||||
Project 2 | 175 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 2.11 | ||||
Project 2 | 692 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 8.34 | ||||
Project 2 | 40 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 0.48 | ||||
Project 2 | 125 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 1.51 | ||||
Project 2 | 20 | Wednesday, June 10, 2020 | Monday, August 31, 2020 | 83 | 0.24 | ||||
Project 3 | 10 | Tuesday, March 10, 2020 | Thursday, March 31, 2022 | 752 | 0.01 | ||||
Project 4 | 1 | Friday, July 31, 2020 | Monday, August 31, 2020 | 32 | 0.03 | ||||
Project 4 | 6 | Monday, July 27, 2020 | Monday, August 31, 2020 | 36 | 0.17 | ||||
Project 4 | 6 | Monday, July 27, 2020 | Monday, August 31, 2020 | 36 | 0.17 | ||||
Project 4 | 4 | Monday, July 27, 2020 | Monday, August 31, 2020 | 36 | 0.11 | ||||
Project 4 | 19 | Monday, February 08, 2021 | Friday, February 26, 2021 | 19 | 1 | ||||
Project 4 | 14.5 | Thursday, October 01, 2020 | Friday, January 08, 2021 | 100 | 0.15 | ||||
Project 4 | 25 | Thursday, October 01, 2020 | Friday, January 08, 2021 | 100 | 0.25 | ||||
Project 4 | 53.5 | Thursday, October 01, 2020 | Friday, January 08, 2021 | 100 | 0.54 | ||||
Project 4 | 18 | Thursday, October 01, 2020 | Friday, January 08, 2021 | 100 | 0.18 | ||||
Project 4 | 18 | Thursday, October 01, 2020 | Friday, January 08, 2021 | 100 | 0.18 | ||||
Project 4 | 19 | Monday, February 08, 2021 | Friday, February 26, 2021 | 19 | 1 | ||||
Project 4 | 66.5 | Monday, February 08, 2021 | Friday, February 26, 2021 | 19 | 3.5 | ||||
Project 5 | 20 | Tuesday, January 19, 2021 | Monday, March 01, 2021 | 42 | 0.48 | ||||
Project 6 | 10 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.33 | ||||
Project 6 | 2 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.07 | ||||
Project 6 | 37.5 | Monday, January 27, 2020 | Wednesday, June 30, 2021 | 521 | 0.07 | ||||
Project 6 | 198 | Tuesday, September 01, 2020 | Tuesday, August 31, 2021 | 365 | 0.54 | ||||
Project 6 | 10 | Tuesday, September 01, 2020 | Saturday, October 31, 2020 | 61 | 0.16 | ||||
Project 6 | 16 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.53 | ||||
Project 6 | 12 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.4 | ||||
Project 6 | 1.6 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.05 | ||||
Project 6 | 1 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.03 | ||||
Project 7 | 45 | Tuesday, September 01, 2020 | Saturday, October 31, 2020 | 61 | 0.74 | ||||
Project 7 | 20 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.38 | ||||
Project 7 | 110 | Tuesday, February 25, 2020 | Saturday, April 30, 2022 | 796 | 0.14 | ||||
Project 7 | 60 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 1.13 | ||||
Project 7 | 23 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.43 | ||||
Project 7 | 35 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.66 | ||||
Project 7 | 16 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.3 | ||||
Project 7 | 2 | Monday, February 17, 2020 | Tuesday, March 31, 2020 | 44 | 0.05 | ||||
Project 7 | 53 | Monday, March 02, 2020 | Sunday, July 31, 2022 | 882 | 0.06 | ||||
Project 7 | 2 | Saturday, February 01, 2020 | Saturday, February 29, 2020 | 29 | 0.07 | ||||
Project 7 | 5 | Monday, February 17, 2020 | Tuesday, March 31, 2020 | 44 | 0.11 | ||||
Project 7 | 5 | Monday, February 17, 2020 | Tuesday, March 31, 2020 | 44 | 0.11 | ||||
Project 7 | 10 | Saturday, February 01, 2020 | Saturday, February 29, 2020 | 29 | 0.34 | ||||
Project 7 | 1.5 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.03 | ||||
Project 7 | 4 | Monday, March 09, 2020 | Thursday, April 30, 2020 | 53 | 0.08 | ||||
Project 7 | 120 | Tuesday, September 01, 2020 | Saturday, October 31, 2020 | 61 | 1.97 | ||||
Project 8 | 3 | Monday, September 21, 2020 | Friday, October 30, 2020 | 40 | 0.08 | ||||
Project 8 | 19 | Monday, September 21, 2020 | Friday, October 30, 2020 | 40 | 0.48 | ||||
Project 8 | 19 | Thursday, October 01, 2020 | Friday, October 30, 2020 | 30 | 0.63 | ||||
Project 8 | 15 | Monday, September 21, 2020 | Friday, October 30, 2020 | 40 | 0.38 | ||||
Project 8 | 16 | Friday, October 30, 2020 | Thursday, December 31, 2020 | 63 | 0.25 | ||||
Project 8 | 30 | Wednesday, September 30, 2020 | Friday, October 30, 2020 | 31 | 0.97 | ||||
Project 8 | 10 | Monday, September 28, 2020 | Friday, October 30, 2020 | 33 | 0.3 | ||||
Project 8 | 15 | Monday, September 21, 2020 | Friday, October 30, 2020 | 40 | 0.38 | ||||
Project 8 | 24 | Monday, September 21, 2020 | Friday, October 30, 2020 | 40 | 0.6 | ||||
Project 8 | 6 | Wednesday, September 30, 2020 | Friday, October 30, 2020 | 31 | 0.19 | ||||
Project 9 | 10 | Monday, June 22, 2020 | Friday, August 20, 2021 | 425 | 0.02 | ||||
Project 9 | 21 | Monday, March 09, 2020 | Thursday, September 30, 2021 | 571 | 0.04 | ||||
Project 9 | 7 | Monday, February 10, 2020 | Friday, February 28, 2020 | 19 | 0.37 | ||||
Project 9 | 2 | Monday, June 22, 2020 | Friday, August 20, 2021 | 425 | 0 | ||||
Project 10 | 2.5 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 0.17 | ||||
Project 10 | 10 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 0.67 | ||||
Project 10 | 20 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 1.33 | ||||
Project 10 | 2.5 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 0.17 | ||||
Project 10 | 2 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.25 | ||||
Project 10 | 12 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 1.5 | ||||
Project 10 | 4 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.5 | ||||
Project 10 | 1 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.13 | ||||
Project 10 | 5 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.63 | ||||
Project 10 | 0.5 | Monday, March 30, 2020 | Friday, April 03, 2020 | 5 | 0.1 | ||||
Project 10 | 8 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 1 | ||||
Project 10 | 18 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 2.25 | ||||
Project 10 | 3 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 0.38 | ||||
Project 10 | 30 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 3.75 | ||||
Project 10 | 3 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 0.38 | ||||
Project 10 | 5 | Monday, March 30, 2020 | Friday, April 03, 2020 | 5 | 1 | ||||
Project 10 | 1 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 0.13 | ||||
Project 10 | 8 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 1 | ||||
Project 10 | 1 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 0.13 | ||||
Project 10 | 6 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 0.4 | ||||
Project 10 | 6 | Friday, March 27, 2020 | Friday, April 03, 2020 | 8 | 0.75 | ||||
Project 10 | 4 | Monday, August 17, 2020 | Monday, August 31, 2020 | 15 | 0.27 | ||||
Project 10 | 4 | Wednesday, March 25, 2020 | Friday, March 27, 2020 | 3 | 1.33 | ||||
Project 10 | 30 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 3.75 | ||||
Project 10 | 2 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.25 | ||||
Project 10 | 2 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.25 | ||||
Project 10 | 1 | Thursday, May 07, 2020 | Thursday, May 14, 2020 | 8 | 0.13 |
You need a measure that filters your report table based on dates. Something like this.
HoursThisDay =
VAR ThisDay =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
CALCULATE (
SUM ( report[HoursPerDay] ),
report[Estimated Work End Date] >= ThisDay,
report[Estimated Work Start Date] <= ThisDay
)
Then you can use a stacked column visual or something similar:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |