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 ...
"DAX"
"Need Help"
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.