Transform Start/End date to Stacked Area timeline?

[Hansss]

Hi,
I have a number of projects with start date and end date stated.

I would like to visualize how many projects are active each month, in a stacked area chart or similar.
Any input on how I should go about this?

Thanks!

[Hansss]

I'll try to give some examples to better explain what I'm looking for.

Project 1

Project	Start_Date	Finish_date	Strategy
P1	2022-01-01	2022-09-01	S1
P2	2022-02-01	2022-09-01	S1
P3	2022-03-01	2022-10-01	S2

With this information I want to create a graph over how many projects are active each month.

Later on I want to divive them by Strategy and make a stacked area chart, but that's step 2 I guess..

[v-jingzhang · Accepted solution]

Hi @Hansss 

You can first add a Calendar (Date) table to the model, then create the following measure. 

Active Projects = 
CALCULATE (
    COUNT ( 'Table'[Project] ),
    'Table'[Start_Date] <= MIN ( 'Calendar'[Date] ),
    'Table'[Finish_date] >= MAX ( 'Calendar'[Date] )
)

Use Calendar's Month column on the X-axis of the stacked area chart. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.