Changing data type to Date/Time inverts "dd" and "mm" position

[dvhez]

The column I have problems with is created by:

DateTimeCMG = FORMAT('CMg 220kV'[Date] + 'CMg 220kV'[HourTime];"dd/mm/yyyy HH:mm")

Being Date, HourTime and DateTimeCMG columns the followings:

Data type: Date - Format: dd/MM/yyyyData type: Date/Time - Format: HH:mmData Type: Text - Format: Text

As you can see, it is everyhing OK if DateTimeCMG column is kept as Text. However, if I change its data type to Date/Time results in this format:

While 'Date' and 'HourTime' columns remains the same for these first 5 rows!. What am I missing?

Thanks!

[Anonymous]

The issue you are facing is related to the DateTime localisation thats occuring.  This could be related to the timezone selected for the PC you are using.  For example if you are using a VM on Azure, it might be set to the US format.

Alternatively there is a 'Locale for Import' in the settings under 'File' -> 'Options and Settings' -> 'Options' -> 'Regional Settings'.  This could be set to "English (United States)".  You might need to set this to something else such as "English (Australia)"

[dvhez · Accepted solution]

Thanks for your answer!. However, I solved this by changing date format into "General Date" instead of "dd/mm/yyyy HH:mm"

DateTimeCMG = FORMAT('CMg 220kV'[Date] + 'CMg 220kV'[HourTime];"General Date")

[Anonymous]

Great, glad you solved it!  Just be aware that the FORMAT function will return a datatype of String (Text).  This can be perfectly fine if you convert it later on in your process back into a Date Type, or are only using the Date in a report display.  While it is in text format, date time intelligence cannot be applied to the column.