Comparing dates but handling results with no data in one column

[Anonymous]

Hi,

Probably a really bad title but couldn't quite figure out how to word it.

I am wanting to report on how many of our projects have achieved their target KPI of a go live date. So currently I have;

In KPI = IF([Servicecommencementdate] > [Targetgolivedate], "No", "Yes")

However some of the results in Servicecommencementdate have no data because the project has not gone live yet. Ideally I'd like the projects that have not yet gone live and have missed their target date to show "No", with "Yes" until that time happens but suspect that might not be possible.

If it isn't possible, how could I have it so the In KPI column is left blank for those projects that have yet to have anything added to the Servicecommencementdate column?

Thanks.

[sturlaws · Accepted solution]

Hi @Anonymous ,

does this code give you your desired outcome:

In KPI =
SWITCH (
    TRUE (),
    ISBLANK ( [Servicecommencementdate] )
        && TODAY () <= [Targetgolivedate], "Yes",
    ISBLANK ( [Servicecommencementdate] )
        && TODAY () > [Targetgolivedate], "No",
    [Servicecommencementdate] > [Targetgolivedate], "No",
    [Servicecommencementdate] <= [Targetgolivedate], "Yes",
    "Other"
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Sturla thanks very much that is great. Out of interest what is the "other"?

Thanks.

[sturlaws]

The last statement of the switch-function is the ELSE-value, if none of the the other statements in the switch-statement evaluates to true, use this value. I prefer to use something else than blank, so I just randomly choose to use "Other". Having something else than blank sometimes makes it easier to troubleshoot.