Forum Discussion
Dax Formula to Create Column Based on Filtered Data
Hello,
I hope I am able to explain correctly what I am trying to do.
I have the following Column in my Data "Last Comment" and I need a New Column that sorts the data and outputs one of 3 values. AKA create 3 buckets. The first bucket would be "Good" meaning the date is in the future and is in the right format (MM/DD FUP).
The Second bucket would be "Outdated" meaning it is in the right format, but the date is in the past. Finally, the third bucket would be "Invalid" meaning the format is incorrect. These dates will change so I would need to use relative date information so the formula will work for future data sets in this format.
Thank you in advance for any assistance!!
Hi Anonymous ,
You can create a calculated column as below to get it:
Column = VAR CurrentDate = TODAY () VAR CommentDate = IFERROR ( DATE ( YEAR ( CurrentDate ), LEFT ( 'Table'[Last Comment], 2 ), MID ( 'Table'[Last Comment], 4, 2 ) ), BLANK () ) RETURN SWITCH ( TRUE (), ISBLANK ( CommentDate ), "Invalid", CommentDate > CurrentDate, "Good", CommentDate <= CurrentDate, "Outdated" )Best Regards
2 Replies
Hi Anonymous ,
You can create a calculated column as below to get it:
Column = VAR CurrentDate = TODAY () VAR CommentDate = IFERROR ( DATE ( YEAR ( CurrentDate ), LEFT ( 'Table'[Last Comment], 2 ), MID ( 'Table'[Last Comment], 4, 2 ) ), BLANK () ) RETURN SWITCH ( TRUE (), ISBLANK ( CommentDate ), "Invalid", CommentDate > CurrentDate, "Good", CommentDate <= CurrentDate, "Outdated" )Best Regards