I have a very weird problem. I have built a tabular model which takes data from a SSAS cube. In the data source cube, there is a column called "Process Date" and it is in UK date format (dd/mm/yyyy). When I bring this column into my model, the dates are messed up, where the date and month values are swapped for some dates!
Below are the images to illustrate this and I have highlighted one particular date in red. In the data source SSAS cube, the date is 12/02/2019, and when it goes to the tabular model, this date beomces 02/12/2019. I have added a check_month column in the tabular model and found that the tabular model thinks its December i.e. the day/month has been swapped!
Thank you
JC
Edit: This has been solved by changing the Locale Identifier in the connection. See the comment in the answer below by userfl89. This is due to the data source cube is using a different locale (US English) then what I am using in the model, changing the locale identifier will override this and hence solve the problem
The date format can be defined from SSDT. Highlight the date column and go to the properties window (press F4). For the Data Format property select the desired date format. If you need a date format that's not listed a calculated column using the FORMAT function can be created based off the original column, with the data type of this column then set to Date. An example of this is below. Additionally, confirm the Locale Identifier (LCID) in SSDT. This can by viewed by selecting Model > Existing Connections > Edit > Build > All > then the Locale Identifier property. The Microsoft documentation provides details in regards to identifying the correct LCID.
=FORMAT('Process'[Process Date], "dd-MM-yyyy")