Forum Discussion

Kyle_Wolfe_Ohio's avatar
Kyle_Wolfe_Ohio
Regular Visitor
2 years ago
Solved

Complex IF or ISBLANK

I have a pretty complex script & I am stuck on the final WHERE component. The following image is straight from the table that captures the "Date Filled" and "Date Vacated" information I need to use for some reports on our organization's positions. 

 

The first row indicates that the position was vacated and refilled = Filled

The second row indicates a brand new position that has never been filled or vacated = Vacant

The third and fourth rows indicate the position has only been occupied once and never vacated = Filled

 

I have compiled the following, but it is NOT showing the second row in the image above as vacant and I am not sure how to include it in the formula:

 

Is Vacant? =
VAR vToday =
    TODAY ()
VAR vResult =
    SWITCH (
        TRUE,
        NOT ISBLANK('Stale Pos PersNet PCR'[Date Vacated]), IF('Stale Pos PersNet PCR'[Date Vacated] >='Stale Pos PersNet PCR'[Date Filled], "Yes", "No"),
        ISBLANK ('Stale Pos PersNet PCR'[Date Vacated]), "No", "Yes"
)
RETURN
vResult
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Kyle_Wolfe_Ohio Maybe: 

    Is Vacant? =
VAR vToday =
    TODAY ()
VAR vResult =
    SWITCH (
        TRUE,
        [Date Filled] = BLANK() && [Date Vacated] = BLANK(), "Yes",
        [Date Vacated] <> BLANK() && [Date Vacated] >= [Date Filled], "Yes",
        "No"
    )
RETURN
vResult

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Kyle_Wolfe_Ohio Maybe: 

    Is Vacant? =
VAR vToday =
    TODAY ()
VAR vResult =
    SWITCH (
        TRUE,
        [Date Filled] = BLANK() && [Date Vacated] = BLANK(), "Yes",
        [Date Vacated] <> BLANK() && [Date Vacated] >= [Date Filled], "Yes",
        "No"
    )
RETURN
vResult