cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Help Urgent|| How to divide hours for a project over start and end dates

Hello-

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
2 REPLIES 2
Community Support

Hi, @jen8080

Best Regards,

Caiyun Zheng

Super User

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:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors