Forum Discussion

Josh1994's avatar
Josh1994
Helper II
4 years ago
Solved

Need help - Comparing two dates (or blank data)

Hi!

 

I am trying to make a new column based on two others.

Essentially, it is checking that the "actual completed date" is less than or equal to the target date.

 

The bit im having an issue with, is i also need it to flag up if there is no data in the Actual completed date field, and the target completed date is after todays date.

I include a small photo to help visualise what i am trying toa chieve

 

As always, I appreciate any advice you are able to give me


Thank you for reading!

 

Josh

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Josh1994,

     

    Try this calculated column:

     

    Compliance = 
VAR vToday =
    TODAY ()
VAR vResult =
    SWITCH (
        TRUE,
        NOT ISBLANK ( Table1[Actual Completed date] ), IF ( Table1[Actual Completed date] <= Table1[Target date], "Yes", "No" ),
        ISBLANK ( Table1[Actual Completed date] ) && Table1[Target date] > vToday, "Yes", "No"
    )
RETURN
    vResult

     

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Josh1994,

     

    Try this calculated column:

     

    Compliance = 
VAR vToday =
    TODAY ()
VAR vResult =
    SWITCH (
        TRUE,
        NOT ISBLANK ( Table1[Actual Completed date] ), IF ( Table1[Actual Completed date] <= Table1[Target date], "Yes", "No" ),
        ISBLANK ( Table1[Actual Completed date] ) && Table1[Target date] > vToday, "Yes", "No"
    )
RETURN
    vResult