Sum data within a date range

[Quarto]

Hello Community!

First time poster here. I have read the guidance on how to ask questions so hopefully I have provided enough information - but please let me know if not!

My data looks like this: 

Project Name	JN	Client	Stage	Status	Combined Status	Start Date	Weeks	End Date	FTE
Project 1	123456	D	Stage 5/6	Appointed	Stage 5/6 Appointed	05/12/2023	5	09/01/2024	0.5
Project 1	123456	D	Stage 5/6	Not Appointed	Stage 5/6 Not Appointed	15/01/2024	15	29/01/2024	0.75
Project 2	789012	B	Stage 3	Appointed	Stage 3 Appointed	12/11/2023	15	26/11/2023	0.75
Project 3	345678	F	Stage 1/2	Appointed	Stage 1/2 Appointed	18/03/2024	30	16/04/2024	0.4
Project 3	345678	F	Stage 1/2	Not Appointed	Stage 1/2 Not Appointed	17/04/2024	25	21/05/2024	0.6
Project 4	901234	A	Stage 5/6	Appointed	Stage 5/6 Appointed	01/02/2024	40	11/04/2024	1
Project 5	567890	C	Stage 4	Appointed	Stage 4 Appointed	28/02/2024	10	08/05/2024	0.6
Project 5	567890	C	Stage 4	Not Appointed	Stage 4 Not Appointed	19/05/2024	20	07/10/2024	0.8
Project 6	234567	E	Stage 1/2	Appointed	Stage 1/2 Appointed	15/12/2023	25	07/06/2024	0.8
Project 6	234567	E	Stage 4	Appointed	Stage 4 Appointed	08/06/2024	30	07/07/2024	0.6
Project 7	345678	F	Stage 4	Appointed	Stage 4 Appointed	21/02/2024	20	17/04/2024	0.5
Project 8	678901	A	Stage 5/6	Appointed	Stage 5/6 Appointed	10/03/2024	35	14/05/2024	0.7
Project 9	789012	C	Stage 1/2	Appointed	Stage 1/2 Appointed	01/11/2023	30	30/11/2023	0.6
Project 9	789012	C	Stage 1/2	Appointed	Stage 1/2 Appointed	01/12/2023	20	19/01/2024	0.4
Project 9	789012	C	Stage 3	Not Appointed	Stage 3 Not Appointed	20/01/2024	15	03/02/2024	0.5
Project 10	890123	D	Stage 3	Appointed	Stage 3 Appointed	05/02/2024	20	25/03/2024	0.7

I plotted the projects on a Gantt chart and assigned the FTE value as the 'resource' so that it could be viewed on the associated data bar. But I would like to be able to calculate the sum of the FTE values at any given date:

I'd like to be able to plot FTE on a stacked area chart or something similar. Ideally I'd also be able to see this sum at the bottom of a visual like the Gantt chart one above, but I'm not sure if that's possible? 

I have a calendar table in my report and the following relationships:

Thanks in advance!

[Quarto]

Hi Luca,

Thanks for your help. Here is the link for wetransfer: https://we.tl/t-PeWboKmcz4

Any help would be greatly appreciated!

[lucadelicio]

thank you for the file but for me it's not clear th result that you want.
Can you explain better or put some images? tx

[Quarto]

Hi Luca,

Apologies. Here is what I would like to see on the report. The first is a stacked area chart with FTE on the y-axis and the Date on the x-axis. 

It would also be great if I could get something like the Gantt chart visual I used here but with a way to sum the FTE at intervals of one week, as I've added to the image below.

Thank you for your help with this!

[lucadelicio · Accepted solution]

Hi think you are a little bit confused.
First of all you have 2 dates. Start and end.
So wich date you want to use to show in the stacked area chart?
If you use the start date the result will be this:

As attachment the pbix.
For the second if you don't explain the way to split the FTE values in week how you can will do it?
Before go in power bi you have to set all the logical rules.
Then with the rules set you can go on power bi to do what you need.
The right visual for your data is wath you have yet built.
If you have a value with a range you have to use the gantt.

[lucadelicio]

Hi, Please put the pbix file (better) or the data file xlsx on wetransfer.com, and the image of the result that you want.

So the community will help you easly.

Thank you