Showing 3 / 4 / 5 months before GLD

[romovaro]

Hello

I want to crate a table showing the amount of projects by PM and date.

Customer	Project	Country Long Name	PM	Actual/Schedule Go Live
Client A	NCPFFR01	France	John Smith	1/1/2024
Client B	ASPNFR01	France	Ludovic Garcia	4/1/2024
Client B	ASPNDE01	Germany	Ludovic Garcia	4/1/2024
Client B	ASPNKR01	Korea, Republic of	Ludovic Garcia	4/1/2024
Client B	ASPNMY01	Malaysia	Ludovic Garcia	4/1/2024
Client B	ASPNRU01	Russia	Ludovic Garcia	4/1/2024
Client B	ASPNES01	Spain	Ludovic Garcia	4/1/2024
Client B	ASPNCN01	China	Ludovic Garcia	4/1/2024
Client C	TNSGTH01	Thailand	Klaudia Fernandez	5/1/2024
Client D	2181	Switzerland	Jean Claude	6/1/2024
Client D	2208	France	Pierre	2/1/2025

I need to create a table showing months that PMs are busy with a project. Usually PMs start the projects 3-4 months before Go live date. the idea would be to show something like this:

	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24
Ludovic	7	7	7	7
Klaudia Fernandez		1	1	1	1

Ludovic has 7 projects going live in April 2024. That means he is busy starting Jan 2024 (Go live date - 3 months). 

Klaudia has 1 project in May 2024, therefore busy from Feb to Apr.

Any idea would be welcome

thanks

[3CloudThomas]

Create a calculated column for NewDate

3MonthsPrior = DATEADD('Date'[Date], -3, MONTH)

Create a measure 
NumOfProjects = COUNT( 'Project'[ProjectId])

Place on a Matrix visual with PM on Rows, 3MonthsPrior on columns and NumOfProjects in value.

[romovaro]

thanks 3cloudThomas,

I thought about this approach but in only shows the total projects 3 months before the GLD date.

with your formula I see the "kick off date project" in pink.

And currently my report is showing GLD in red.

The view I would like to achieve is to show total projects from starting to GLD.

In the case of Ludovic, also showing -2 Month and -1 month

	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24
Ludovic	7	7	7	7
Klaudia Fernandez		1	1	1	1

Thanks,

Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Ludovic7777   Klaudia Fernandez 1111  

[3CloudThomas]

You could have a row in a fact table for each month and if the project is active.