Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

If else then Otherwise in calculated column

Hello all,

 

 

i created a calculated column in my Date Table to realize this rules:


 

 

This is my current Calculated Column formula:

 

Column =
IF('DATE'[Date] = 'DATE'[Yesterday], 'DATE'[Yesterday],
IF('DATE'[Date] = 'DATE'[Current Date], 'DATE'[Current Date],
THEN "Other Day")

)

 

 

But unfortunatly i get a error as return value on the Column.

 

 

Any suggestions? I 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    From your screenshot, you created a custom column in Query Editor rather than creating a calculated colunmn using DAX formula. And your date column should be Date type date, while you type the Yesterday and Current Date(they are text date), so it returns error because date equals text type(like 2018/1/22=Current Date).

    I create a sample table. Today is 2018/1/22.



    In Query Editor, I click the yesterday and today in Calendar(highlighted in red circle), it will return correct result.

    add conditionresult

    If you want to create a calculated column using DAX instead of Query statement, you can create it using the formula.

    Calculated column =
IF (
    Table1[Date] = TODAY (),
    "Current Date",
    IF (
        Table1[Date]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 ),
        "Yesterday",
        "Other Day"
    )
)


    Please see the result as follows.



    Best Regards,
    Angelia

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    From your screenshot, you created a custom column in Query Editor rather than creating a calculated colunmn using DAX formula. And your date column should be Date type date, while you type the Yesterday and Current Date(they are text date), so it returns error because date equals text type(like 2018/1/22=Current Date).

    I create a sample table. Today is 2018/1/22.



    In Query Editor, I click the yesterday and today in Calendar(highlighted in red circle), it will return correct result.

    add conditionresult

    If you want to create a calculated column using DAX instead of Query statement, you can create it using the formula.

    Calculated column =
IF (
    Table1[Date] = TODAY (),
    "Current Date",
    IF (
        Table1[Date]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 ),
        "Yesterday",
        "Other Day"
    )
)


    Please see the result as follows.



    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you so much Angelia !

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I had the same case and I did it just like in excel and it worked. Since the output in this case is a string not a column then we can just add it like that. 

    First "if" is first condition, 2nd "if" is else of the first if, then final else is empty 

    Column = 

    IF('DATE'[Date] = 'DATE'[Yesterday], "Yesterday", IF('DATE'[Date] = 'DATE'[Current Date], "Current Date", "")