Forum Discussion
Create a "Project Completion Date" Column Based on Most Recent Activity Completion Date in Project
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!
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:
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 :
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)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!