cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

7 REPLIES 7
Anonymous
Not applicable

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

If yes, please mark my solution Accepted!
Frequent Visitor

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

Community Support

Hi, @mhanne

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
Not applicable

@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"))

If yes, please mark my solution Accepted!
Frequent Visitor

@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
Not applicable

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

Frequent Visitor

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!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors