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

[jen8080]

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

[v-deddai1-msft]

Hi @jen8080 ,

I don't think it will work if we don't summary your data by project. Because we don't have any defined column  to

 distinguish the different requests in the same study. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @jen8080 ,

You can create a summary table :

Summary = ADDCOLUMNS(SUMMARIZE(Projects,Projects[Project ],"_Requested Hours",SUM(Projects[Requested Hours]),"_Estimated Work Start Date",MIN(Projects[Estimated Work Start Date]),"_Estimated Work End Date",MAX(Projects[Estimated Work End Date])),"hourspermonth",CALCULATE(DIVIDE(SUM(Projects[Requested Hours]), DATEDIFF( MIN(Projects[Estimated Work Start Date]),MAX(Projects[Estimated Work End Date]),MONTH) +1)))

Then create a monthyear column in calendar table:

MONTHYEAR = MONTH('Calendar'[Date])/10+YEAR('Calendar'[Date]) 

 At last you can use the following measure:

Measure = SUMX(VALUES('Calendar'[MONTHYEAR]),CALCULATE(SUM('Summary'[hourspermonth]),FILTER('Summary',var a = 'Summary'[_Estimated Work Start Date] var b = 'Summary'[_Estimated Work End Date]  return MONTH(a)/10+YEAR(a)<= MAX('Calendar'[MONTHYEAR])&&MONTH(b)/10+YEAR(b)>= MAX('Calendar'[MONTHYEAR]))))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfdPWVTD_mBLt1lJjS...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jen8080]

Hi @v-deddai1-msft,

Is there a measure to span this based on days, but then still review it in the month format? Also how would I account if an estimated work start date started mid month? Is there a way to make the daily or monthly amount adjusted based on when the work starts or ends in the month? Thanks for the help!!

[v-deddai1-msft]

Hi @jen8080 ,

Just based on your sample data, I can't write an efficient dax formula to work out it. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jen8080]

Hello @v-deddai1-msft ,

We are close but I am getting slightly different results then expected. I am thinking this is because the data is summed by project and then by min and max estimated dates. I need it to be eveluated row by row and not all summed together by study, the reason is because the dates can be very different by request even though they might for the same study but the time frame is not the same. So when taking the min and max date you are expanding requested hours out over the whole length vs the defined time (estimated work start and work end date). Is there anyway to eleveate it row by row based on each rows dates instead of summing by project by project?

[parry2k]

@jen8080 solution attached, you can tweak it as per your needs.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡