New and Old Projects

As I see it, you have 3 cases.

A - A project is New
B - A project is Ongoing
C - A Project is Closed

I would suggest breaking these out into a flag that says "Closed", "Ongoing", "Open" that you can then use in any visualisation, say, a table.

The calculated column could be created with this:

Status = 

VAR CurrentProjectID = 'Projects'[ProjectID]

VAR LatestMonth = MAX('Projects'[Date])

VAR FirstAppearance = 

 CALCULATE(

 MIN('Projects'[Date]),

 FILTER(

 ALL('Projects'),

 'Projects'[ProjectID] = CurrentProjectID

 )

 )

VAR LastAppearance = 

 CALCULATE(

 MAX('Projects'[Date]),

 FILTER(

 ALL('Projects'),

 'Projects'[ProjectID] = CurrentProjectID

 )

 )

VAR IsInLatestMonth = LastAppearance = LatestMonth

VAR IsFirstInLatestMonth = FirstAppearance = LatestMonth




RETURN

SWITCH(

 TRUE(),

 NOT(IsInLatestMonth), "Closed",

 IsFirstInLatestMonth, "New",

 "Ongoing"

)

 This assumes the table is called Projects, the ID is ProjectID, and the date is Date.

Test Data

Sample Output

Edit : correction of "Closed", "Ongoing", "Closed" to have one "Open"