How to group and display pivot data by months for unstructured periods that cover several months

[BatEmo]

Hi Guys,
I have an interesting case that is almost making me start drinking…..
I have an OCM (organizational change management) plan, containing the following columns:

• Task
• Task Category
• Start Date
• End Date
• Stakeholder Group
• Expected Commitment (in hours)

I have linked the table to a pivot and created a Line chart from the pivot. Next steps are to add slicers and timelines, so the whole thing is a bit more interactive (and have a complete dashboard with different metrics). Goal is to be able to present to the Management team what commitment is needed from the different stakeholder groups on a monthly basis.

The problem is that many of the tasks that I have in my plan involve weekly meetings and the start and end date are in different months. So, for example I have a task, called “Weekly Meeting with the Business SMEs”, where e.g. start is 02.01.2022 and end date is 09.05.2022. Those meetings will be 1 hour long, so I enter 18 hours as total commitment needed from the SMEs for the above period. The goal is to get the relevant number of hours e.g. for March, when I select March in the timeline. If I link the whole thing with the start, or the end date of the period, it shows me 18 hours needed in Jan, or respectively in May, whereas I need it to take into consideration the Expected Commitment and the number of months in the period and show e.g. 4 hours for Jan, 4 hours for Feb, 4 for March, 4 for April and 2 for May.
Any ideas how I can do this?
Thanks,
Emo

 

[BSALV]

disappointing, but if your “Weekly Meeting with the Business SMEs” is every week on friday for 1 hour, the best option is to add a row on every friday instead of 1 row for the whole project = 18 rows instead of 1 !
In that way, the hours for every month are assigned to the right month.

 

[BatEmo]

I guess that could be an option, yes. That's a good plan B, thanks!