Forum Discussion
Microsoft EmployeeSpreading values over time (total requested hours over time)
Hi there, I hope everybody is doing great.
I've been trying to crack this nut for some hours but I'm not finding the right way to do it - so I rather ask for help.
the situation is simple - I have projects that have start and end dates , total requested hours and I want to be able to spread the requested hours over the months since the project start till it ends. For example a project that has 100hrs requested over two months , I want to be able to put that into a visual but instead of seeing 100hrs on the starting date of the project , I want to be able to see 50 hrs on month 1 and 50 hrs on month 2. if the project runs for 10 months and its a 1000 hrs project , then I want to be able to see 100 hrs x month over the 10 months of the project. unfortunately there's no granularity on the data I'm getting and I don't know how create that table .
I'm attaching a picture of the issue (not the solution but I hope the explanation makes it easy to follow). and adding a simple table with the minimum data I guess we need to create a solution (the model is way to big and sensitive to share more than this).
thanks in advance
| Project. | Requested Resource Start Date | Requested Resource End Date | Requested Hours |
| 2206550 | 7/1/2020 | 6/30/2021 | 574 |
| 2245570 | 7/1/2020 | 6/30/2021 | 160 |
| 2059200 | 7/13/2020 | 9/12/2020 | 120 |
| 2185104 | 7/20/2020 | 4/2/2021 | 360 |
| 2221184 | 7/20/2020 | 11/7/2020 | 92 |
| 2226726 | 7/27/2020 | 9/26/2020 | 100 |
| 2249584 | 7/28/2020 | 11/6/2020 | 88 |
| 2249592 | 7/28/2020 | 11/6/2020 | 84 |
| 2249598 | 7/28/2020 | 11/6/2020 | 40 |
| 2274084 | 8/2/2020 | 6/30/2021 | 800 |
| 2224700 | 8/3/2020 | 3/19/2022 | 264 |
| 1938284 | 8/31/2020 | 11/29/2020 | 424 |
| 2129676 | 8/31/2020 | 10/16/2020 | 240 |
| 2264512 | 8/31/2020 | 10/16/2020 | 160 |
| 2264522 | 8/31/2020 | 10/16/2020 | 120 |
| 1982174 | 9/7/2020 | 12/31/2020 | 240 |
| 2140432 | 9/14/2020 | 5/31/2021 | 24 |
| 2197694 | 9/14/2020 | 12/31/2020 | 400 |
| 2273416 | 9/14/2020 | 12/11/2020 | 320 |
| 2310614 | 9/14/2020 | 10/16/2020 | 80 |
| 2235826 | 9/21/2020 | 11/27/2020 | 320 |
| 2317108 | 9/21/2020 | 5/5/2021 | 142 |
| 2317122 | 9/21/2020 | 5/5/2021 | 400 |
| 1264874 | 9/28/2020 | 10/2/2020 | 40 |
| 2317120 | 9/28/2020 | 5/5/2021 | 80 |
| 2314392 | 9/30/2020 | 12/30/2020 | 240 |
| 2152510 | 10/1/2020 | 12/11/2020 | 558 |
| 2152550 | 10/1/2020 | 12/11/2020 | 306 |
| 1946164 | 10/5/2020 | 6/30/2021 | 200 |
| 1982174 | 10/5/2020 | 10/16/2020 | 240 |
| 2129676 | 10/5/2020 | 7/5/2022 | 176 |
| 2215598 | 10/5/2020 | 11/14/2020 | 60 |
| 2230812 | 10/5/2020 | 1/8/2021 | 720 |
| 2365568 | 10/12/2020 | 4/25/2021 | 252 |
| 2353882 | 10/13/2020 | 1/29/2021 | 192 |
| 1929570 | 10/19/2020 | 11/27/2020 | 240 |
| 2215580 | 10/19/2020 | 2/5/2021 | 292 |
| 2355482 | 10/19/2020 | 12/19/2020 | 248 |
| 2365586 | 10/19/2020 | 4/25/2021 | 140 |
| 2365594 | 10/19/2020 | 4/25/2021 | 248 |
| 2365818 | 10/20/2020 | 5/9/2021 | 96 |
| 2365922 | 10/20/2020 | 4/25/2021 | 708 |
3 Replies
- amitchandak
Super User
v-kkf-msftCommunity Support
Hi,
Try the following formula:
Value = var MonthDiff = DATEDIFF( MAX(PROJECT[Requested Resource End Date]), MAX(PROJECT[Requested Resource Start Date]), MONTH ) var _average = DIVIDE( MAX(PROJECT[Requested Hours]), -MonthDiff ) var EndDate = EDATE( MAX(PROJECT[Requested Resource Start Date]), -MonthDiff-1 ) return IF( MAX('Calendar'[Date]) >= MAX(PROJECT[Requested Resource Start Date]) && MAX('Calendar'[Date]) <= EndDate, _average, BLANK() )This is my PBIX file.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DSiffredithanks for taking the time, unfortunately this doesn't do what I was looking for and is sensitive to date granularity which shouldnt be.
thanks again!
