Monthly workload from project plan in Power BI dashboard

[DataOverlord]

Hi I have a table of planned work hours with start and end date. I want to distribute work hours based on the dates and have a dashboard showing each employee's monthly planned workload. Here is a rough sketch of the input table, output table and logic table. Can you please help me create the calculations required to do this? I created a measure that does the calc but I could only get the values to populate at the end date. I am new to PBI. I am guessing I need new intermediate column to store values and then calculate from there?

Input Table
Name	Ticket ID	Start Date	End Date	Planned Hours
Alex	1	1/5/24	11/16/24	110
Alex	2	2/17/24	10/16/24	90
Alex	3	5/15/24	12/16/24	80
Alex	4	6/29/24	12/16/24	70
Alex	5	3/3/24	6/29/24	40
Alex	6	1/1/24	12/16/24	120
Ryan	7	1/1/24	12/24/24	120
Ryan	8	1/1/24	6/29/24	70
Output Table - Monthly planned workload
Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Alex	20	30	40	40	50	60	50	50	50	50	40	30
Ryan	20	20	20	20	20	20	20	10	10	10	10	10
Output Table Calculation Explanation
Name	Ticket ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Alex	1	10	10	10	10	10	10	10	10	10	10	10
Alex	2		10	10	10	10	10	10	10	10	10
Alex	3					10	10	10	10	10	10	10	10
Alex	4						10	10	10	10	10	10	10
Alex	5			10	10	10	10
Alex	6	10	10	10	10	10	10	10	10	10	10	10	10
Ryan	7	10	10	10	10	10	10	10	10	10	10	10	10

[Anonymous]

HI @DataOverlord,

it sounds like a common requirement about analyze records from the date ranges that define by multiple date fields.

I'd like to suggest you take a look the following link start, end date part if helps:
Before You Post, Read This: start, end date 

In addition, you can also try to create a expand mapping table to the detail records that include in the date ranges and link to the raw table. Then you can simply use them to create visualizations:

Solved: Spread revenue across period based on start and en... - Microsoft Fabric Community

Regards,

Xiaoxin Sheng