Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |