Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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
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", "")
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.
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
Thank you so much Angelia !
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |