Project End date based on sub tasks end date

[Prabha45]

Hi,
I have two tables Parent and child table. Parent child has Parent ID, start date. Child table has Parent ID,Child ID,start date,status and comment date column. The child end date is the comment date, if the status is completed.

Parent ID	Child ID	Start date	Status	Comment date	End date
1	a	1/2/2000	Completed	2/3/2000	2/3/2000
1	b	10/3/2000	Completed	15/6/2000	15/6/2000
2	c	20/4/2000	Draft	30/4/2000
2	d	5/5/2000	Completed	6/7/2000	6/7/2000
2	e	10/11/2000	Inprogress	20/11/2000
3	f	20/5/2000	Completed	1/10/2000	1/10/2000
3	g	6/10/2000	Completed	1/12/2000	1/12/2000

How to calculate the parent end date ?
The parent end date for 1 should be 15/6/2000 and for 3 is 1/12/2000.
Thanks in advance

[Anonymous]

Hi @Prabha45 ,

I suggest you to try code as below to create a measure.

End Date = 
VAR _LIST =
    SUMMARIZE (
        'Parent',
        'Parent'[Parent ID],
        "Count",
            VAR _STATUSLIST =
                CALCULATETABLE (
                    VALUES ( Child[Status] ),
                    FILTER ( ALL ( Child ), Child[Parent ID] = EARLIER ( [Parent ID] ) )
                )
            RETURN
                IF ( COUNTX ( _STATUSLIST, [Status] ) = 1 && "Completed" IN _STATUSLIST, 1, 0 )
    )
VAR _COUNT =
    MAXX ( FILTER ( _LIST, [Parent ID] = MAX ( [Parent ID] ) ), [Count] )
RETURN
    IF (
        _COUNT = 1,
        CALCULATE ( MAX ( Child[Comment date] ), ALLEXCEPT ( Child, Child[Parent ID] ) )
    )  

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alef_Ricardo_]

Hi Prabha45,

You can calculate the parent end date by finding the maximum comment date for each parent ID where the status is completed. Here’s an example of how you can do this using SQL:

SQL

SELECT ParentID, MAX(CommentDate) as EndDate
FROM ChildTable
WHERE Status = 'Completed'
GROUP BY ParentID;

This query will return a table with two columns: ParentID and EndDate. The EndDate column will contain the maximum comment date for each parent ID where the status is completed. You can then join this table with your parent table to get the end date for each parent.

I hope this helps! Let me know if you have any further questions. 😊