cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jen8080
Helper I
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 DaysHoursPerDay
Project 1 4 Wednesday, December 16, 2020 Wednesday, December 23, 2020 80.5
Project 1 20 Wednesday, December 09, 2020 Wednesday, December 23, 2020 151.33
Project 2 12 Wednesday, June 10, 2020 Monday, August 31, 2020 830.14
Project 2 50 Wednesday, June 10, 2020 Monday, August 31, 2020 830.6
Project 2 175 Wednesday, June 10, 2020 Monday, August 31, 2020 832.11
Project 2 692 Wednesday, June 10, 2020 Monday, August 31, 2020 838.34
Project 2 40 Wednesday, June 10, 2020 Monday, August 31, 2020 830.48
Project 2 125 Wednesday, June 10, 2020 Monday, August 31, 2020 831.51
Project 2 20 Wednesday, June 10, 2020 Monday, August 31, 2020 830.24
Project 3 10 Tuesday, March 10, 2020 Thursday, March 31, 2022 7520.01
Project 4 1 Friday, July 31, 2020 Monday, August 31, 2020 320.03
Project 4 6 Monday, July 27, 2020 Monday, August 31, 2020 360.17
Project 4 6 Monday, July 27, 2020 Monday, August 31, 2020 360.17
Project 4 4 Monday, July 27, 2020 Monday, August 31, 2020 360.11
Project 4 19 Monday, February 08, 2021 Friday, February 26, 2021 191
Project 4 14.5 Thursday, October 01, 2020 Friday, January 08, 2021 1000.15
Project 4 25 Thursday, October 01, 2020 Friday, January 08, 2021 1000.25
Project 4 53.5 Thursday, October 01, 2020 Friday, January 08, 2021 1000.54
Project 4 18 Thursday, October 01, 2020 Friday, January 08, 2021 1000.18
Project 4 18 Thursday, October 01, 2020 Friday, January 08, 2021 1000.18
Project 4 19 Monday, February 08, 2021 Friday, February 26, 2021 191
Project 4 66.5 Monday, February 08, 2021 Friday, February 26, 2021 193.5
Project 5 20 Tuesday, January 19, 2021 Monday, March 01, 2021 420.48
Project 6 10 Thursday, October 01, 2020 Friday, October 30, 2020 300.33
Project 6 2 Thursday, October 01, 2020 Friday, October 30, 2020 300.07
Project 6 37.5 Monday, January 27, 2020 Wednesday, June 30, 2021 5210.07
Project 6 198 Tuesday, September 01, 2020 Tuesday, August 31, 2021 3650.54
Project 6 10 Tuesday, September 01, 2020 Saturday, October 31, 2020 610.16
Project 6 16 Thursday, October 01, 2020 Friday, October 30, 2020 300.53
Project 6 12 Thursday, October 01, 2020 Friday, October 30, 2020 300.4
Project 6 1.6 Thursday, October 01, 2020 Friday, October 30, 2020 300.05
Project 6 1 Thursday, October 01, 2020 Friday, October 30, 2020 300.03
Project 7 45 Tuesday, September 01, 2020 Saturday, October 31, 2020 610.74
Project 7 20 Monday, March 09, 2020 Thursday, April 30, 2020 530.38
Project 7 110 Tuesday, February 25, 2020 Saturday, April 30, 2022 7960.14
Project 7 60 Monday, March 09, 2020 Thursday, April 30, 2020 531.13
Project 7 23 Monday, March 09, 2020 Thursday, April 30, 2020 530.43
Project 7 35 Monday, March 09, 2020 Thursday, April 30, 2020 530.66
Project 7 16 Monday, March 09, 2020 Thursday, April 30, 2020 530.3
Project 7 2 Monday, February 17, 2020 Tuesday, March 31, 2020 440.05
Project 7 53 Monday, March 02, 2020 Sunday, July 31, 2022 8820.06
Project 7 2 Saturday, February 01, 2020 Saturday, February 29, 2020 290.07
Project 7 5 Monday, February 17, 2020 Tuesday, March 31, 2020 440.11
Project 7 5 Monday, February 17, 2020 Tuesday, March 31, 2020 440.11
Project 7 10 Saturday, February 01, 2020 Saturday, February 29, 2020 290.34
Project 7 1.5 Monday, March 09, 2020 Thursday, April 30, 2020 530.03
Project 7 4 Monday, March 09, 2020 Thursday, April 30, 2020 530.08
Project 7 120 Tuesday, September 01, 2020 Saturday, October 31, 2020 611.97
Project 8 3 Monday, September 21, 2020 Friday, October 30, 2020 400.08
Project 8 19 Monday, September 21, 2020 Friday, October 30, 2020 400.48
Project 8 19 Thursday, October 01, 2020 Friday, October 30, 2020 300.63
Project 8 15 Monday, September 21, 2020 Friday, October 30, 2020 400.38
Project 8 16 Friday, October 30, 2020 Thursday, December 31, 2020 630.25
Project 8 30 Wednesday, September 30, 2020 Friday, October 30, 2020 310.97
Project 8 10 Monday, September 28, 2020 Friday, October 30, 2020 330.3
Project 8 15 Monday, September 21, 2020 Friday, October 30, 2020 400.38
Project 8 24 Monday, September 21, 2020 Friday, October 30, 2020 400.6
Project 8 6 Wednesday, September 30, 2020 Friday, October 30, 2020 310.19
Project 9 10 Monday, June 22, 2020 Friday, August 20, 2021 4250.02
Project 9 21 Monday, March 09, 2020 Thursday, September 30, 2021 5710.04
Project 9 7 Monday, February 10, 2020 Friday, February 28, 2020 190.37
Project 9 2 Monday, June 22, 2020 Friday, August 20, 2021 4250
Project 10 2.5 Monday, August 17, 2020 Monday, August 31, 2020 150.17
Project 10 10 Monday, August 17, 2020 Monday, August 31, 2020 150.67
Project 10 20 Monday, August 17, 2020 Monday, August 31, 2020 151.33
Project 10 2.5 Monday, August 17, 2020 Monday, August 31, 2020 150.17
Project 10 2 Thursday, May 07, 2020 Thursday, May 14, 2020 80.25
Project 10 12 Thursday, May 07, 2020 Thursday, May 14, 2020 81.5
Project 10 4 Thursday, May 07, 2020 Thursday, May 14, 2020 80.5
Project 10 1 Thursday, May 07, 2020 Thursday, May 14, 2020 80.13
Project 10 5 Thursday, May 07, 2020 Thursday, May 14, 2020 80.63
Project 10 0.5 Monday, March 30, 2020 Friday, April 03, 2020 50.1
Project 10 8 Thursday, May 07, 2020 Thursday, May 14, 2020 81
Project 10 18 Friday, March 27, 2020 Friday, April 03, 2020 82.25
Project 10 3 Friday, March 27, 2020 Friday, April 03, 2020 80.38
Project 10 30 Thursday, May 07, 2020 Thursday, May 14, 2020 83.75
Project 10 3 Friday, March 27, 2020 Friday, April 03, 2020 80.38
Project 10 5 Monday, March 30, 2020 Friday, April 03, 2020 51
Project 10 1 Friday, March 27, 2020 Friday, April 03, 2020 80.13
Project 10 8 Friday, March 27, 2020 Friday, April 03, 2020 81
Project 10 1 Friday, March 27, 2020 Friday, April 03, 2020 80.13
Project 10 6 Monday, August 17, 2020 Monday, August 31, 2020 150.4
Project 10 6 Friday, March 27, 2020 Friday, April 03, 2020 80.75
Project 10 4 Monday, August 17, 2020 Monday, August 31, 2020 150.27
Project 10 4 Wednesday, March 25, 2020 Friday, March 27, 2020 31.33
Project 10 30 Thursday, May 07, 2020 Thursday, May 14, 2020 83.75
Project 10 2 Thursday, May 07, 2020 Thursday, May 14, 2020 80.25
Project 10 2 Thursday, May 07, 2020 Thursday, May 14, 2020 80.25
Project 10 1 Thursday, May 07, 2020 Thursday, May 14, 2020 80.13
2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

Hi, @jen8080 

Is your problem solved?

 

Best Regards,

Caiyun Zheng

AlexisOlson
Super User
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:

AlexisOlson_0-1614115973886.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.