Date YYYYMMDD to DD/MM/YYYY & filtering

[kzielinska]

Hi,

I have a date folumn with format YYYYMMDD. I tried to change its format to dd/mm/yyyy by creating new column using the dax formula:

Date1 = DATE(LEFT([column],4), MID([column], 5, 2), RIGHT([column], 2)),

but it doesn't work due to this error: Cannot convert value 'null' of type Text to type Integer.

I tried to replace nulls with the measure: 

Date if blank = IF(ISBLANK([column]),0,[column]),

but it does not work.

Does anyone know fow to fix this?

[ppm1]

If your column is type text, you can do it in the query editor with a custom column with this expression.

= Date.ToText(Date.From([Date]), "dd/MM/yyyy")

Pat

[amitchandak]

@kzielinska , try like

Date if blank = IF(ISBLANK([column]),blank() ,DATE(LEFT([column],4), MID([column], 5, 2), RIGHT([column], 2)) )

[kzielinska]

I get this error: An argument of function 'DATE' has the wrong data type or the result is too large or too small.