topic Re: How to create a code to only publish a final date if all actions is closed in DAX Commands and Tips

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

gjelstrup — Thu, 22 Sep 2022 14:45:40 GMT

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.

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

johnt75 — Thu, 22 Sep 2022 15:40:31 GMT

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

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

gjelstrup — Thu, 22 Sep 2022 19:53:55 GMT

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

)

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

johnt75 — Fri, 23 Sep 2022 08:37:09 GMT

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

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

gjelstrup — Fri, 23 Sep 2022 09:12:19 GMT

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