cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LiamH
Helper I
Helper I

Comparing dates but handling results with no data in one column

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.

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @LiamH ,

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.

 

View solution in original post

3 REPLIES 3
sturlaws
Resident Rockstar
Resident Rockstar

Hi @LiamH ,

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.

 

View solution in original post

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

 

Thanks.

sturlaws
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors