Forum Discussion

varelapablo90's avatar
varelapablo90
Helper II
3 years ago
Solved

IF between DATEs return a TEXT values, how?

Hello community

 

I am having the below issue:

DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

I want to create a new column that checks if something (Clearing Date) happened between a period of 2 dates (Start reporting & End Reporting), in case of True return a text column with the responsible HUB and if false just type "Non Scope"

 

HUB_Scope =
IF (
    T_[Clearing Date]
        >= RELATED ( T_Mapping[Start Reporting] ) & T_[Clearing Date]
        <= RELATED ( T__Mapping[End Reporting] ),
    RELATED ( T_Mapping[HUB_SCOPE] ),
    "NON SCOPE"
)

 

Clearing Date. Start Reporting and End Reporting are Dates but HUB_Scope isnt.

 

How can I can I make work the above formula ??

 

Thanks

condition
dates
DAX
if
Text
  • johnt75's avatar
    johnt75
    3 years ago

    You're using & which is the concatenation operator, you need to use && which is logical AND.

    HUB_Scope =
IF (
    T_[Clearing Date] >= RELATED ( T_Mapping[Start Reporting] )
        && T_[Clearing Date] <= RELATED ( T__Mapping[End Reporting] ),
    RELATED ( T_Mapping[HUB_SCOPE] ),
    "NON SCOPE"
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You're using & which is the concatenation operator, you need to use && which is logical AND.

    HUB_Scope =
IF (
    T_[Clearing Date] >= RELATED ( T_Mapping[Start Reporting] )
        && T_[Clearing Date] <= RELATED ( T__Mapping[End Reporting] ),
    RELATED ( T_Mapping[HUB_SCOPE] ),
    "NON SCOPE"
)