Forum Discussion

DanielTraub's avatar
DanielTraub
Regular Visitor
4 years ago
Solved

Compare Dates with IF Function

Hi all, 
 
I have a table with some start and end dates in it. I want to compare those dates, to today. If today is within the start/end date time frame, then return a result of "in learning period". If it's before or after the period, then return results of Upcoming Pilot and Pilot in progress, respectively. See statement below. 
 
Learning Period = IF(Installations[Date Learning Period Started] <= TODAY() <= Installations[Expected Learning Period End Date], "In Learning Period",
                    IF(TODAY() > Installations[Expected Learning Period End Date], "Pilot in progress",
                       IF(Installations[Date Learning Period Started] > TODAY(), "Upcoming/Pilot Prep")
                    )
)  
 
But I keep running into the following error: 
DAX comparison operations do not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.
 
How do I compare the dates but then make it work with the True/False values? 
  • johnt75's avatar
    johnt75
    4 years ago
    Learning Period =
IF (
    Installations[Date Learning Period Started] <= TODAY ()
        && TODAY () <= Installations[Expected Learning Period End Date],
    "In Learning Period",
    IF (
        TODAY () > Installations[Expected Learning Period End Date],
        "Pilot in progress",
        IF (
            Installations[Date Learning Period Started] > TODAY (),
            "Upcoming/Pilot Prep"
        )
    )
)

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi DanielTraub 

    please use

    Learning Period =
VAR TodayDate =
    TODAY ()
RETURN
    IF (
        Installations[Date Learning Period Started] <= TodayDate
            && Installations[Expected Learning Period End Date] >= TodayDate,
        "In Learning Period",
        IF (
            TodayDate > Installations[Expected Learning Period End Date],
            "Pilot in progress",
            IF (
                Installations[Date Learning Period Started] > TodayDate,
                "Upcoming/Pilot Prep"
            )
        )
    )
  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago
    Learning Period =
IF (
    Installations[Date Learning Period Started] <= TODAY ()
        && TODAY () <= Installations[Expected Learning Period End Date],
    "In Learning Period",
    IF (
        TODAY () > Installations[Expected Learning Period End Date],
        "Pilot in progress",
        IF (
            Installations[Date Learning Period Started] > TODAY (),
            "Upcoming/Pilot Prep"
        )
    )
)
    • DanielTraub's avatar
      DanielTraub
      Regular Visitor
      4 years ago

      This seems to work! Didn't realize I needed that &&. Thanks so much!