Populating data between two dates for each row

[redlakes]

Hello all,

I am trying to visualize some data and having problems displaying it. What I'd like to show is how much time per day is spent on average on various projects. My work center takes work requests and will spend a few days completing each request. Those requests have fields for an ID, Start Date, End Date, Actual Hours, Project, and others that may not be relevant to this discussion. I have a calculated column for Average Hours Per day that is equal to Actual Hours/(NETWORKDAYS(Start Date, End Date)).

I'm thinking that a stacked column chart is the best way to visualize this data. I have the Y axis as my AvgHoursPerDay and the legend as Project, but the issue comes down to my x-axis. I can select something like End Date or Start Date, but then the data displayed isn't useful. For example, if I have a request from 1/1/23 to 1/10/23 and spend 30 hours on it, my average hours per day is 3. I can get 3 to show up on the 1st or the 10th but that isn't meaningful. Is there way to populate 3 for each day between 1/1 and 1/10? I've also tried to implement a ribbon chart but I have the same issue.

Thanks in advance for the help.

[Anonymous]

Hi @redlakes ,

I created a sample pbix file(see the attachment), please check if that is what you want... You can create Gantt visual and put the field [AvgHoursPerDay] onto the Tooltips field.

Best Regards

[redlakes]

Yingyinr,

Thank you for your reply. The gant chart is helpful for visualizing when we're working on certain jobs tied to different projects, but it doesn't give me the visual of how much each job "weighs" in terms of hours in a day. We're trying to prove resource needs for my work center.

What I'm essentially trying to do is add the start and end date functionality of a gant chart (first picture) with the visualiation that a line chart provides. My second graph is really what I want to show, but it can only show the total hours per job on the start or end date, which isn't very meaningful. A good alternative would be a bar graph (third picture) if I could find a way to add the average hours per day to each day that each job was active. Again, the way it looks right now shows that on December 8th we spent 95 hours on something, when in reality those 95 hours were spent over the course of 3 weeks.

I'm thinking I will probably have make a custom visualization for this, or create a separate table in my model with measured/calculated values and then use the already existing visualizations.