Forum Discussion
New Column using IF and DATESBETWEEN causing Error
I'm trying to create a set of columns that show either the date or null value based off an IF and DATESBETWEEN calculation. Essentially, I need to create a column for each year that is respresented in the a_CreateDateOnly column.
The a_CreateDateOnly column is formatted as a Date.
Here is some sample data along with my desired output..
| Desired Output | |||
| a_CreatedDateOnly | a_CDO=2018Only | a_CDO=2017Only | a_CDO=2016Only |
| 6/18/2016 0:00 | 6/18/2016 0:00 | ||
| 7/11/2016 0:00 | 7/11/2016 0:00 | ||
| 11/14/2016 0:00 | 11/14/2016 0:00 | ||
| 11/14/2016 0:00 | 11/14/2016 0:00 | ||
| 6/18/2017 0:00 | 6/18/2017 0:00 | ||
| 6/18/2017 0:00 | 6/18/2017 0:00 | ||
| 6/19/2017 0:00 | 6/19/2017 0:00 | ||
| 11/14/2017 0:00 | 11/14/2017 0:00 | ||
| 6/18/2018 0:00 | 6/18/2018 0:00 | ||
| 6/18/2018 0:00 | 6/18/2018 0:00 | ||
| 6/19/2018 0:00 | 6/19/2018 0:00 | ||
| 6/20/2018 0:00 | 6/20/2018 0:00 |
I created the following formula to try and get my desired output...
a_CDO_2018Only = IF(DATESBETWEEN('Case'[a_CreatedDateOnly],DATE(2018,01,01),Date(2018,12,31)),'Case'[a_CreatedDateOnly], "null")
This results in the error - Expressions that yield variant data-type cannot be used to define calculated columns.
Anonymous,
Just use DAX below.
Column = IF ( YEAR ( 'Case'[a_CreatedDateOnly] ) = 2018, 'Case'[a_CreatedDateOnly] )
2 Replies
Anonymous,
Just use DAX below.
Column = IF ( YEAR ( 'Case'[a_CreatedDateOnly] ) = 2018, 'Case'[a_CreatedDateOnly] )