Forum Discussion

heejinyune's avatar
heejinyune
Frequent Visitor
3 years ago

Problem with cannot convert text into date type

I have a table [example] like this: submit_date close_date ID 04/01/2022 08/01/2022 A123 05/06/2023 14/07/2023 A124   and my dax code to return the table which have date differen...
Martin_D's avatar
Martin_D
Solution Sage
3 years ago

Hi heejinyune ,
The easier place to reliably convert date as text to date as date-datatype is in Power Query using the "with locale" type conversion.
In DAX you can replace all occurrences of 

 

DATEVALUE ( 'Table'[Date Text] )

 

with

 

DATE ( 
    VALUE ( RIGHT ( 'Table'[Date Text], 4 ) ), 
    VALUE ( LEFT ( RIGHT ( 'Table'[Date Text], 7 ), 2 ) ), 
    VALUE ( LEFT ( 'Table'[Date Text], 2 ) ) 
)

 

The behavior of DATEVALUE depends on the Culture property of your dataset, so some users might have the same problem and some not. With my de-de culture there was no problem as you described. Anyway, with the code above you are an the save side, as long as your data format is always two digits day,two digits month,4 digits year.