How to create a code to only publish a final date if all actions is closed

[gjelstrup]

I have the below dataset, where there is several actions (Code 005) for each notification. I would like to create a column that will publish a final date for closure of the actions - but only if all actions for the notification has a comepled date. There is different number of action tasks for each notification. 

I need it to publish the latest date an action was closed for each notification. 

Typ	Notifctn	Completed	Code	Action tasks closed	Expected result
ZP	200017902	15-jun-22	0005	15.jun.2022	30 jun22
ZP	200017902	30-jun-22	0005	30.jun.2022	30 jun22
ZP	200018394	05-jul-22	0005	05-jul-22	05-jul22
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet.
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet.
ZP	200000153		0005		no date as one action is not closed yet.
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet.

I Really hope that someone can help me out here 🙂 Thank you very much in advance for your help. 

[gjelstrup]

Thank you so much for your kind support. It works perfect now. 

[johnt75]

You can add a column like

Final completed date = 
VAR CompletedDates =
    CALCULATETABLE (
        VALUES ( 'Table'[Action tasks closed] ),
        ALLEXCEPT ( 'Table', 'Table'[Notifctn] )
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( 'Table'[Action tasks closed] ),
            ALLEXCEPT ( 'Table', 'Table'[Notifctn] )
        )
    )

[gjelstrup]

Thank you very much for your quick support. I have entered the formula into my dataset. It does not give me any errors, but it also does not give me any dates. 🤔

I think now I understand why. In my dataset there will also be other lines with codes "006" and "007" that will not be completed. Therefore it gives me empty fields in all cases. Can you advise in this more complex situation also?

Typ	Notifctn	Completed	Code	Action tasks closed	Expected result
ZP	200017902	15-jun-22	0005	15.jun.2022	30 jun22
ZP	200017902	30-jun-22	0005	30.jun.2022	30 jun22
ZP	200017902		0006		either empty or  30-jun22
ZP	200017902		0007		either empty or  30-jun22
ZP	200018394	05-jul-22	0005	05-jul-22	05-jul22
ZP	200018394	05-jul-22	0006		either empty or  05-jul22
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet.
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet.
ZP	200000153		0005		no date as one action is not closed yet.
ZP	200000153	27-apr-21	0005	27.apr.2021	no date as one action is not closed yet
ZP	200000153		0006		no date as one action is not closed yet.
ZP	200000153		0007		no date as one action is not closed yet.

Final actions completed =

VAR CompletedDates =

    CALCULATETABLE (

        VALUES (QM13[Action tasks closed]),

        ALLEXCEPT (QM13,QM13[Notifctn])

    )

RETURN

    IF (

        NOT BLANK () IN CompletedDates,

        CALCULATE (

            MAX ( QM13[Action tasks closed]),

            ALLEXCEPT (QM13,QM13[Notifctn])

        )

    )

[johnt75 · Accepted solution]

You can make a tweak to make sure that the code is also taken into account

Final actions completed =
VAR CompletedDates =
    CALCULATETABLE (
        VALUES ( QM13[Action tasks closed] ),
        ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
    )
RETURN
    IF (
        NOT BLANK () IN CompletedDates,
        CALCULATE (
            MAX ( QM13[Action tasks closed] ),
            ALLEXCEPT ( QM13, QM13[Notifctn], QM13[Code] )
        )
    )