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

[mhanne]

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 ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status
1	a	Complete	Mike	10/1	Complete
1	b	Complete	Mike	10/3	Complete
1	a	Complete	Brent	10/1	Complete
1	b	Complete	Brent	10/5	Complete
2	a	Complete	Mike	10/3	Active
2	b	Complete	Mike	10/4	Active
2	a	Complete	Brent	10/3	Active
2	b	Active	Brent		Active

Any help would be appreciated! 

[mhanne]

My data looks like this

Project ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status
1	a	Complete	Mike	10/1	Complete
1	b	Complete	Mike	10/3	Complete
1	a	Complete	Brent	10/1	Complete
1	b	Complete	Brent	10/5	Complete
2	a	Complete	Mike	10/3	Active
2	b	Complete	Mike	10/4	Active
2	a	Complete	Brent	10/3	Active
2	b	Active	Brent		Active

Any help would be appreciated! 

[Anonymous]

@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]

@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 ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status	Project Completed Date
1	a	Complete	Mike	10/1	Complete	10/5
1	b	Complete	Mike	10/3	Complete	10/5
1	a	Complete	Brent	10/1	Complete	10/5
1	b	Complete	Brent	10/5	Complete	10/5
2	a	Complete	Mike	10/3	Complete	10/8
2	b	Complete	Mike	10/4	Complete	10/8
2	a	Complete	Brent	10/3	Complete	10/8
2	b	Complete	Brent	10/8	Complete	10/8

Thank you so much for your help!

[mhanne]

Project ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status	Project Completed Date
1	a	Complete	Mike	10/1	Complete	10/5
1	b	Complete	Mike	10/3	Complete	10/5
1	a	Complete	Brent	10/1	Complete	10/5
1	b	Complete	Brent	10/5	Complete	10/5
2	a	Complete	Mike	10/3	Complete	10/8
2	b	Complete	Mike	10/4	Complete	10/8
2	a	Complete	Brent	10/3	Complete	10/8
2	b	Complete	Brent	10/8	Complete	10/8

Thank you so much for your help!

[v-easonf-msft]

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

[Anonymous]

@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!

[mhanne]

@Anonymous 

Here is the formula when I use my own fields:

It is still giving me the same date in every row. When I turned off Auto date/time intelligence it gave me a number rather than a date and would not let me change the Data Type (when I tried the table wouldn't work until I removed the column). 

I'm also a bit confused by your example, because the completion dates are switched in your table ( Project 1 should be 10/5/2020 and Project 2 should be 10/8/2020)

[Anonymous · Accepted solution]

@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]

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 ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status
1	a	Complete	Mike	10/1	Complete
1	b	Complete	Mike	10/3	Complete
1	a	Complete	Brent	10/1	Complete
1	b	Complete	Brent	10/5	Complete
2	a	Complete	Mike	10/3	Active
2	b	Complete	Mike	10/4	Active
2	a	Complete	Brent	10/3	Active
2	b	Active	Brent		Active

Any help would be appreciated! 

[mhanne]

My data looks like this

Project ID	Activity ID	Activity Status	Person Assigned	Activity Completion Date	Project Status
1	a	Complete	Mike	10/1	Complete
1	b	Complete	Mike	10/3	Complete
1	a	Complete	Brent	10/1	Complete
1	b	Complete	Brent	10/5	Complete
2	a	Complete	Mike	10/3	Active
2	b	Complete	Mike	10/4	Active
2	a	Complete	Brent	10/3	Active
2	b	Active	Brent		Active

Any help would be appreciated!