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 syst...
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!
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!