Forum Discussion
Help! Dynamic measure based on start and end dates!
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. I need it to be dynamic enough to understand if it started mid-month it would then have less hours then if it was a full month.
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.
Example: requested hours is 80 the estimated work start date in 15-Jun-20 and Estimated work end date is 24-Aug-20 the measure would span the requested hours our accordingly.
Jun-20 | Jul-20 | Aug-20 |
19 | 35 | 26 |
Notes:
1. I have already created a separate date table called "Calendar" with these columns
Calendar = CALENDAR(MIN(report[Estimated Work Start Date]),MAX(report[Estimated Work End Date]))
MONTHYEAR = MONTH('Calendar'[Date])/10+YEAR('Calendar'[Date])
2. I already have a measure that spans data out evenly per month vs what I need which is for it to be dynamic based on dates
Measure 2 = SUMX(VALUES('Calendar'[MONTHYEAR]),CALCULATE(SUM(report[HoursPerMonth]),FILTER('report',var a = 'report'[Estimated Work Start Date]var b = 'report'[Estimated Work End Date] return MONTH(a)/10+YEAR(a)<= MAX('Calendar'[MONTHYEAR])&&MONTH(b)/10+YEAR(b)>= MAX('Calendar'[MONTHYEAR]))))
3.. Each project can have multiple requested hours (that’s why the same project is listed multiple times)
I have a dynamic key get a unique ID by row
4. I cannot share the data it is sensitive but I have created a sample set below
| Project | Requested Hours | Estimated Work End Date | Estimated Work Start Date | Work Start Vs Work End Days | Requested Hours per Day | HoursPerMonth | KEY |
| Project 1 | 743.7 | 30-Apr-21 | 31-Jul-20 | 274 | 2.71 | 74.37 | Key 1 |
| Proejct 2 | 60 | 19-Jun-20 | 1-Jun-20 | 19 | 3.16 | 60 | Key 2 |
| Project 3 | 138 | 1-Jan-21 | 1-Jun-20 | 215 | 0.64 | 17.25 | Key 3 |
| Project 4 | 10 | 20-Aug-21 | 22-Jun-20 | 425 | 0.02 | 0.67 | Key 4 |
| Project 4 | 2 | 20-Aug-21 | 22-Jun-20 | 425 | 0 | 0.13 | Key 5 |
| Project 5 | 72 | 12-Oct-20 | 27-Jul-20 | 78 | 0.92 | 18 | Key 6 |
| Project 5 | 90 | 12-Oct-20 | 27-Jul-20 | 78 | 1.15 | 22.5 | Key 7 |
| Project 5 | 40 | 12-Oct-20 | 27-Jul-20 | 78 | 0.51 | 10 | Key 8 |
| Project 5 | 48 | 12-Oct-20 | 27-Jul-20 | 78 | 0.62 | 12 | Key 9 |
| Project 5 | 24 | 12-Oct-20 | 27-Jul-20 | 78 | 0.31 | 6 | Key 10 |
| Project 5 | 390 | 12-Oct-20 | 27-Jul-20 | 78 | 5 | 97.5 | Key 11 |
| Project 5 | 16 | 12-Oct-20 | 27-Jul-20 | 78 | 0.21 | 4 | Key 12 |
| Project 5 | 4 | 12-Oct-20 | 27-Jul-20 | 78 | 0.05 | 1 | Key 13 |
| Project 5 | 8 | 12-Oct-20 | 27-Jul-20 | 78 | 0.1 | 2 | Key 14 |
| Project 5 | 16 | 12-Oct-20 | 27-Jul-20 | 78 | 0.21 | 4 | Key 15 |
| Project 5 | 50 | 12-Oct-20 | 27-Jul-20 | 78 | 0.64 | 12.5 | Key 16 |
| Project 5 | 4 | 12-Oct-20 | 27-Jul-20 | 78 | 0.05 | 1 | Key 17 |
| Project 5 | 12 | 12-Oct-20 | 27-Jul-20 | 78 | 0.15 | 3 | Key 18 |
| Project 5 | 8 | 12-Oct-20 | 27-Jul-20 | 78 | 0.1 | 2 | Key 19 |
| Project 5 | 45.5 | 12-Oct-20 | 27-Jul-20 | 78 | 0.58 | 11.38 | Key 20 |
| Project 5 | 124.2 | 12-Oct-20 | 27-Jul-20 | 78 | 1.59 | 31.05 | Key 21 |
| Project 5 | 39 | 12-Oct-20 | 27-Jul-20 | 78 | 0.5 | 9.75 | Key 22 |
| Project 5 | 8 | 12-Oct-20 | 27-Jul-20 | 78 | 0.1 | 2 | Key 23 |
| Project 5 | 30 | 12-Oct-20 | 27-Jul-20 | 78 | 0.38 | 7.5 | Key 24 |
| Project 5 | 340 | 12-Oct-20 | 27-Jul-20 | 78 | 4.36 | 85 | Key 25 |
| Project 5 | 68 | 12-Oct-20 | 27-Jul-20 | 78 | 0.87 | 17 | Key 26 |
| Project 5 | 40 | 12-Oct-20 | 27-Jul-20 | 78 | 0.51 | 10 | Key 27 |
| Project 6 | 7.5 | 5-Jun-20 | 1-Jun-20 | 5 | 1.5 | 7.5 | Key 28 |
| Project 6 | 4.5 | 5-Jun-20 | 1-Jun-20 | 5 | 0.9 | 4.5 | Key 29 |
| Project 7 | 4 | 17-Jul-20 | 14-Jul-20 | 4 | 1 | 4 | Key 30 |
| Project 7 | 10 | 17-Jul-20 | 14-Jul-20 | 4 | 2.5 | 10 | Key 31 |
| Project 7 | 6 | 17-Jul-20 | 14-Jul-20 | 4 | 1.5 | 6 | Key 32 |
| Project 7 | 5 | 17-Jul-20 | 14-Jul-20 | 4 | 1.25 | 5 | Key 33 |
| Project 7 | 2 | 17-Jul-20 | 14-Jul-20 | 4 | 0.5 | 2 | Key 34 |
| Project 8 | 10 | 1-Jul-22 | 1-Jul-20 | 731 | 0.01 | 0.4 | Key 35 |
| Project 9 | 0.5 | 30-Jun-20 | 26-Jun-20 | 5 | 0.1 | 0.5 | Key 36 |
| Project 9 | 1 | 30-Jun-20 | 26-Jun-20 | 5 | 0.2 | 1 | Key 37 |
| Project 10 | 2 | 30-Jul-20 | 27-Jul-20 | 4 | 0.5 | 2 | Key 38 |
| Project 10 | 10 | 31-Jul-20 | 8-Jul-20 | 24 | 0.42 | 10 | Key 39 |
| Project 10 | 3 | 19-Jun-20 | 8-Jun-20 | 12 | 0.25 | 3 | Key 40 |
