changing Date Format

[sathyaramesh]

I have the data with date format as (02.08.2018). By default it taking as Text datatype.

If i change to date format then i am getting error for whole data

How to change into Date format.

I have no problem with date like(02-08-2018) and (02/08/2018)

Thanks,

Ramesh

[Johanno]

I think your regional settings must match the format the use. But if there is no other way you could extract the date from the text using as a calculated column:

Date format = DATE(RIGHT(Table1[Date];4);MID(Table1[Date];4;2);LEFT(Table1[Date];2))

[sathyaramesh]

Now i tried this format Date = DATE(MID(Table1[Date],7,4),MID(Table1[Date],4,2),MID(Table1[Date],1,2))

I am getting date datatype.

Now i need to change the date format manually to this DATE. 

Like "dd/mmm/yy"

How to get that?

[Johanno]

Hi, you almost said it yourself.  Try FORMAT(Datewiththewrongformat;"dd/mm/yy")

[sathyaramesh]

Yeah i tried that too. But if i change format to this newly created Date again it going back to Text Format.

[sathyaramesh]

Getting error message for the format date dax

"Cannot convert value '16.07.2018' of type Text to type Date."

[Johanno]

Sorry, then I don't know. It works over here. I'm guessing that the regional settings must match the date formats you're working with. Hope someone else can help.

[sathyaramesh]

Anyway Thank you so much for all the replay

[v-danhe-msft · Accepted solution]

Hi @sathyaramesh,

Based on my test, it do will convert to text format with "dd/mmm/yy", it may due to your location, you could refer to below blog that may help you:

https://www.oraylis.de/blog/local-date-formats-in-power-bi

Regards,

Daniel He

[sathyaramesh]

Thank you so much