Forum Discussion

Prabha45's avatar
Prabha45
Helper III
3 years ago

Project End date based on sub tasks end date

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 IDChild IDStart dateStatusComment dateEnd date
1a1/2/2000Completed2/3/2000 2/3/2000
1b10/3/2000Completed15/6/200015/6/2000
2c20/4/2000Draft30/4/2000 
2d5/5/2000Completed6/7/2000 6/7/2000
2e10/11/2000Inprogress20/11/2000 
3f20/5/2000Completed1/10/20001/10/2000
3g6/10/2000Completed1/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

"DAX"
"Need Help"

2 Replies

  • Alef_Ricardo_'s avatar
    Alef_Ricardo_
    Resolver II
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.