Error: Cannot convert value '0/01/' of type Text to type Date

[Anonymous]

I am new to PBI and I am trying to combine two columns into one.  I created a calculated column PERIOD which is based on the two original columns: YEAR and MONTH.  I am getting this error: Cannot convert value '0/01/' of type Text to type Date.  Any ideas?

[AllisonKennedy · Accepted solution]

A couple of things:
1) The error is coming because you are getting to the 0 at the bottom of your IF statement - that means some of your months don't match. Have you done a Trim on this column in Power Query to remove any spaces?
2) You could use DATE() for this instead of DATEVALUE since your date parts are already separated. It means you won't have to use the & to concatenate and is a bit simpler to work with.
3) Use SWITCH instead of so many nested IF statements:
Period = DATE(Table[Year],
SWITCH( Table[Month]
, "Jan", 1
, "Feb", 2
, "Mar", 3
, "Apr", 4
, "May", 5
, "Jun", 6
...
),
1)


4) Since this is part of the data model and a calculated column, I would personally do this in Power QUERY, NOT in DAX.
https://radacad.com/m-or-dax-that-is-the-question/?ref=818
https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=1*("1/"&[Month]&"/"&[Year])

Format this column as Date.

[Anonymous]

Thanks, Ashish.  This worked as well.  It took me a minute to realize I needed to add the column name in the formula "Period =1*("1/"&[Month]&"/"&[Year])".  Pretty slick

[Ashish_Mathur]

You are welcome.

[AllisonKennedy · Accepted solution]

A couple of things:
1) The error is coming because you are getting to the 0 at the bottom of your IF statement - that means some of your months don't match. Have you done a Trim on this column in Power Query to remove any spaces?
2) You could use DATE() for this instead of DATEVALUE since your date parts are already separated. It means you won't have to use the & to concatenate and is a bit simpler to work with.
3) Use SWITCH instead of so many nested IF statements:
Period = DATE(Table[Year],
SWITCH( Table[Month]
, "Jan", 1
, "Feb", 2
, "Mar", 3
, "Apr", 4
, "May", 5
, "Jun", 6
...
),
1)


4) Since this is part of the data model and a calculated column, I would personally do this in Power QUERY, NOT in DAX.
https://radacad.com/m-or-dax-that-is-the-question/?ref=818
https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

[Anonymous]

The DATE + SWITCH fix worked beautifully.  Thanks for the quick response and for sending a couple solutions.  I am sending you Virtual Kudos