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

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!

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, mhanne 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

 

Best Regards,
Community Support Team _ Eason