Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
dvhez
Helper II
Helper II

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:

 

Data type: Date - Format: dd/MM/yyyyData type: Date - Format: dd/MM/yyyyData type: Date/Time - Format: HH:mmData type: Date/Time - Format: HH:mmData Type: Text - Format: TextData 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

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

 

 

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
Not applicable

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.