cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ross73312 Super Contributor
Super Contributor

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

 

Ross73312 Super Contributor
Super Contributor

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!


   


Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 317 members 3,327 guests
Please welcome our newest community members: