Forum Discussion

NadineNicolle's avatar
NadineNicolle
Regular Visitor
7 months ago
Solved

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 m...
Need Help
  • JamieHolding's avatar
    JamieHolding
    7 months ago

    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"

JamieHolding's avatar
JamieHolding
Icon for Resolver I rankResolver I
7 months ago

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"

  • NadineNicolle's avatar
    NadineNicolle
    Regular Visitor
    7 months ago

    This works, thanks a lot!😎

    • JamieHolding's avatar
      JamieHolding
      Icon for Resolver I rankResolver I
      7 months ago

      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.