Hello Power BI gurus,
I've been using Power Query and Power BI for a little while now, but I've encountered a challenge that's got me stumped. There might be a much better way to do this using DAX (rather than Power Query), but I'm just starting to learn about DAX so I have no idea if such a method exists or not.
I'm trying to take a table of data like this:
Table 1
... and turn it into a table like this:
Table 2
The idea is to calculate the number of days between the "Start Date" and "End Date" for each row, then create a separate row for each of those dates. The number of rows needed is indicated in the "Days of Work" column (which is simply the number of days between the Start Date and End Date). In this example, I would need 8 rows for Project 1, 4 rows for Project 2, and 5 rows for Project 3.
Then, the number in the "Avg. Hrs. per Day" column is to be distributed across all the rows as shown in the "Projected Hours" column from Table 2.
The idea is that I can then use this table to create a "Running Total" Quick Measure in Power BI. I will overlay this field onto an Area Chart that is tracking the actual number of hours worked on the project. That way we can see if we're over or under the projected number of hours.
In other words, I'm distributing the "Hours Forecast" evenly across all the days in the project (so a 5-day project with 20 hours forecasted would have 4 hours allocated per day for each of the 5 days of the project). This will of course result in a sloping straight-line in my area chart, against which I will be able to see how much we're over/under the projected hours worked. Kind of like a reverse of the "burn-down" chart used in Scrum if you're familiar with that at all.
I hope this makes sense, but if not please ask any clarifying questions you have. And thank you for your insights!
