Problem with assigning / visualizing FTE distributed amounts to months

[synaptical]

Dear community

I have a requirement to distribute and visualize the FTE demand over a timeline. The basis is an Excel File which contains only start and end dates, a role, a phase and total hours and so on. So if an activity goes for several months, I simply divide the FTE value by the number of months (we do not need to be more granular for our purpose) to create "FTE distributed" and now I want to place these values to the appropriate months in the timeline.

I also created a calendar table with MIN/MAX dates:

Calendar = CALENDAR(FIRSTDATE('Assignments'[Start Date]), LASTDATE('Assignments'[End Date]))

The problem is that if I for example filter on a specific role like product manager in my case, it visualizes the amount linear from start until the end, but it should take into account the values per month.

If someone can help me in checking and correcting or enhancing this I am very thankful, as my knowledge (beginner) with Power BI and DAX has reached its current end.

The measure I currently have for the visual Y axis is:

# FTE on Timeline = 
VAR EarlistStartDate = SELECTEDVALUE('Calendar'[Date], MIN('Calendar'[Date]))
VAR LatestEndDate = SELECTEDVALUE('Calendar'[Date], MAX('Calendar'[Date]))
RETURN
IF(
   EarlistStartDate >= MIN('Assignments'[Start Date]) && LatestEndDate <= MAX('Assignments'[End Date]),
   DIVIDE('Assignments'[# SUM FTE Distributed]*100, 'Assignments'[# SUM Work Days Period]),
   0)

The assignment table looks the following (I cannot upload the .pbix and .xlsx, but can send it to you):

Phase	Activity	Role	Quantity	Hours	Total Hours	Start Date	End Date	Assigned Days	Work Days in Period	Months in Period	FTE	FTE Distributed
Management	Activity 1	Product Manager	6	8	48	01.01.2025	30.11.2025	333	238	12	0.3	0.025
Design	Activity 2	Product Manager	1	40	40	01.01.2025	31.01.2025	30	23	1	0.25	0.25
Design	Activity 3	Product Manager	1	20	20	15.01.2025	28.02.2025	44	33	2	0.125	0.0625
Design	Activity 4	UI Designer	1	16	16	15.01.2025	28.02.2025	44	33	2	0.1	0.05
Design	Activity 5	UI Designer	1	12	12	16.02.2025	28.02.2025	12	10	1	0.075	0.075
Design	Activity 6	Architect	1	8	8	01.02.2025	28.02.2025	27	20	1	0.05	0.05
Design	Activity 7	Architect	2	8	16	15.01.2025	14.02.2025	30	23	1	0.1	0.1
Build	Activity 8	UI Designer	1	32	32	01.03.2025	30.04.2025	60	43	2	0.2	0.1
Build	Activity 9	UI Designer	1	8	8	17.03.2025	25.04.2025	39	30	2	0.05	0.025
Build	Activity 10	Engineer	1	20	20	01.03.2025	30.04.2025	60	43	2	0.125	0.0625
Build	Activity 11	Architect	4	4	16	01.03.2025	15.03.2025	14	10	1	0.1	0.1
Build	Activity 12	Product Manager	2	4	8	17.03.2025	25.04.2025	39	30	2	0.05	0.025
Build	Activity 13	UI Designer	1	8	8	01.03.2025	30.04.2025	60	43	2	0.05	0.025
Deploy	Activity 14	UI Designer	1	12	12	01.05.2025	30.06.2025	60	43	2	0.075	0.0375
Deploy	Activity 15	Architect	1	40	40	01.05.2025	20.05.2025	19	14	1	0.25	0.25
Deploy	Activity 16	Architect	1	20	20	25.05.2025	30.06.2025	36	26	2	0.125	0.0625
Deploy	Activity 17	UI Designer	1	16	16	01.05.2025	30.06.2025	60	43	2	0.1	0.05
Deploy	Activity 18	UI Designer	4	4	16	15.05.2025	30.06.2025	46	33	2	0.1	0.05
Deploy	Activity 19	Engineer	1	32	32	01.05.2025	30.06.2025	60	43	2	0.2	0.1
Deploy	Activity 20	Architect	1	4	4	15.06.2025	30.06.2025	15	11	1	0.025	0.025

The data model looks like this:

Many thanks in advance to anyone who can help me.

[lbendlin · Accepted solution]

Not exactly sure where your issue is but here is how I would show the data

fte ass = 
var d = max('Calendar'[Date])
var a = filter(SUMMARIZE(Assignments,[Start Date],[End Date],[FTE]),d in CALENDAR([Start Date],[End Date]))
return sumx(a,[FTE])

Or like this with a modified ribbon chart

[synaptical]

Thank you so much for your solution! This is exactly what I was looking for and I wouldn't be able to develop this myself with my limited skills. Also thank you for the hint to the ribbon chart, it makes much more sense to use this visual.

I tried to recreate the ribbon to look it your way - what exaclty you modified to achieve this?

Again, thank you very very much for your support.