cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dvhez Regular Visitor
Regular Visitor

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

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:

 

PBIDesktop_2018-10-23_00-55-10.pngData type: Date - Format: dd/MM/yyyyPBIDesktop_2018-10-23_00-57-42.pngData type: Date/Time - Format: HH:mmPBIDesktop_2018-10-23_00-58-20.pngData 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:

 

 

PBIDesktop_2018-10-23_01-01-54.png

 

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

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
dvhez Regular Visitor
Regular Visitor

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

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")

 

3 REPLIES 3
Super User
Super User

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

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)"

 

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


dvhez Regular Visitor
Regular Visitor

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

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")

 

Super User
Super User

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

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!