Forum Discussion

mhanne's avatar
mhanne
Frequent Visitor
5 years ago
Solved

Create a "Project Completion Date" Column Based on Most Recent Activity Completion Date in Project

 

Good Morning!

I am trying to create a "Project Completion Date" column to calculate the completion date for a completed project based off of the latest Activity Completion Date. Our system does not track the day a project was marked complete so this is the next best thing. So looking at the data below, when a project's status is "Complete" I want the new Project Completion Date column to display the most recent activity completion date from that project (so for project 1,  it would show a Project Completion Date of 10/5.)

 

 

My data looks like this

Project IDActivity IDActivity Status

Person Assigned

Activity Completion DateProject Status
1aCompleteMike10/1Complete
1bCompleteMike10/3Complete
1aCompleteBrent10/1Complete
1bCompleteBrent10/5Complete
2aCompleteMike10/3Active
2bCompleteMike10/4Active
2aCompleteBrent10/3Active
2bActiveBrent Active

Any help would be appreciated! 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    mhanne The reverse date is due to condition with "Earlier". Fixed the DAX as following:

    ProjectCompletionDate = CALCULATE(MAX(Sheet5[Activity Completion Date]),
    FILTER(Sheet5, Sheet5[Project Status]="Complete"
    && Sheet5[Project ID]=EARLIER(Sheet5[Project ID])))
     
     
    Did I solve your problem?
    If yes, please give kudos and mark my reply Accepted!

7 Replies

  • mhanne's avatar
    mhanne
    Frequent Visitor
    5 years ago

    I'm not sure why the post is overlapping the title but here is the content of the post: 

     

    Good Morning!

    I am trying to create a "Project Completion Date" column to calculate the completion date for a completed project based off of the latest Activity Completion Date. Our system does not track the day a project was marked complete so this is the next best thing. So looking at the data below, when a project's status is "Complete" I want the new Project Completion Date column to display the most recent activity completion date from that project (so for project 1,  it would show a Project Completion Date of 10/5.)

     

     

    My data looks like this

    Project IDActivity IDActivity Status

    Person Assigned

    		Activity Completion DateProject Status
    1aCompleteMike10/1Complete
    1bCompleteMike10/3Complete
    1aCompleteBrent10/1Complete
    1bCompleteBrent10/5Complete
    2aCompleteMike10/3Active
    2bCompleteMike10/4Active
    2aCompleteBrent10/3Active
    2bActiveBrent Active

    Any help would be appreciated! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    mhanne Please create a calculated column with following DAX:

    Project Completion Date = IF(Table3[Project Status]= "Complete", MAX(Table3[Activity Completion Date]))
     
    This will give the desired result.
     
     
     
    Did I answer your question?
    If yes, please mark my solution Accepted!
    • mhanne's avatar
      mhanne
      Frequent Visitor
      5 years ago

      Anonymous 

      When I do this, it fills the entire row with a random date that is not the completion date for any activities. I need the Project completion date to be unique to the projects based on the last completed activity in each project. So if there are 2 projects, I would want the Project Completed Date Column to say 10/5 for project 1 and 10/8 for project two. Like this: 

       

       

      Project IDActivity IDActivity Status

      Person Assigned

      		Activity Completion DateProject StatusProject Completed Date
      1aCompleteMike10/1Complete10/5
      1bCompleteMike10/3Complete10/5
      1aCompleteBrent10/1Complete10/5
      1bCompleteBrent10/5Complete10/5
      2aCompleteMike10/3Complete10/8
      2bCompleteMike10/4Complete10/8
      2aCompleteBrent10/3Complete10/8
      2bCompleteBrent10/8Complete10/8

       

      Thank you so much for your help!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        mhanne  try disabling the Auto date/time intellignece from Options to avoid max date of the year. Please note the DAX does not have ".[Date]". You can try the following DAX :

        ProjectCompletionDate =
        CALCULATE(MAX(Sheet5[Activity Completion Date]),
        FILTER(Sheet5,
        Sheet5[Project ID]<>EARLIER(Sheet5[Project ID])
        && Sheet5[Project Status]="Complete"))
         
        Did I solve your problem?
        If yes, please mark my solution Accepted!