Forum Discussion
New and Old Projects
Hi PowerBI experts!
I am working on a visual that should show which new projects started, and which ones are gone and thus closed, compared with last month.
The data is as follows: I have a monthly data dump of the project list. that comes on top of the previous months. The projects that are not there anymore in the new month, are closed. And any new project numbers that were not in the previous month, are new. I have a column of the months date, when the data dumps were done, and I have a column with project numbers.
Let me know if anything is unclear. Looking very much forward to any suggestions!! 🙂
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"
5 Replies
- JamieHolding
Resolver I
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"
This works, thanks a lot!😎
- JamieHolding
Great!
I had to make some assumptions on what your data looks like, my solution works by checking against a single latest Date, so functions independantly of the actual month - i.e. If a new project appeared in a cut of data showing 01/01/2026, then you did another cut today, it would be treated as "ongoing" since it has appeared before.
I think this shouldn't be an issue based on your description, but the solution would be a bit more complex if you want to allow for this - you'd want to create a column like
MonthStart = STARTOFMONTH('Projects'[Date])first and then run the long calculated column on that one instead. Hope this makes sense.
- krishnakanth240
Super User
Could you please share sample data to work on the logic you have shared. Thank You!
Thanks for the reply, I already fixed it with the solution of JamieHolding !
