This project has moved. For the latest updates, please go here.

ORDER By [Date] Localisation

Sep 1, 2016 at 4:48 AM
Hi,
I'm having troubles with ORDER BY [Date]
My localisation uses a date format of dd/mm/yyyy (Australia) yet DAX Studio appears to assume that all Dates are mm/dd/yyyy - unless that doesn't work - in which case it automatically switches localisation.
If i evaluate Day("01/01/2016"), Day("02/01/2016") ... Day(12/01/2016"), Day("13/01/2016") I get the sequence "1","1" ... "1","13","14", etc,., etc.
At this stage, it looks like the only solution is to parse the Date and ORDER BY YEAR([Date], VALUE(MID([Date],4,2)), VALUE(LEFT([DATE],2))
It works, but it's seems a bit naff.
Any other suggestions?
Coordinator
Sep 1, 2016 at 6:55 AM
DAX Studio does not make any assumptions about dates or do any sorting of data itself. We just send the query back to the source server.

What data type is your [Date] column? if it's a DateTime it should sort fine regardless of the localization. If your [Date] column is a string you need to include a DateTime column in your query and then sort by that.
Sep 2, 2016 at 12:26 AM
Thanks for the response!
You're right! I was trying to sort on a string DataType. (D'oh!) All working now. Thank you. (Using PowerBI Desktop as the data source. btw)
The Day() function did catch me out through and doesn't seem to behaving as it should.
According to the docs, if the argument is a text representation, it should use the localisation of the local machine to interpret the date.

If I run:-
EVALUATE
ADDCOLUMNS (
UNION (
    ROW ( "Date", "02/01/2016" ),
    ROW ( "Date", "01/02/2016" ),
    ROW ( "Date", "13/01/2016" ),
    ROW ( "Date", "01/13/2016" )
),
"Day", DAY ( [Date] )
)

I get:-
Date Day
02/01/2016 1
01/02/2016 2
13/01/2016 13
01/13/2016 13
(The date/time localisation on my machine is dd/mm/yyyy)

In other words, it's not the localisation of the local machine that's driving the interpretation, it appears to be an assuming a date format, unless that doesn't parse.
This would seem to suggest a bug somewhere?
Sep 3, 2016 at 7:12 AM
Hi LesSch,

In the Gregorian Calendar there is no month 13, I suspect that instead of a bug it's a helping and correcting hand that's in play here.
Just a thought.

Best regards
Coordinator
Sep 4, 2016 at 12:11 PM
@Oxenskiold - the problem here is that it's not consistent. It will treat the data as mm/dd/yyyy until it does not work then it tries dd/mm/yyyy

So 12/9/2016 is treated as the 9th of December and the next day (in dd/mm/yyyy format) 13/9/2016 gets treated as 13th of September. If I have a choice I now use the ISO format for date strings "yyyy-mm-dd" as that is always parsed consistently. But this problem is not unique to DAX, T-SQL has a similar behaviour that attempts to be helpful. But at least the CONVERT() function in T-SQL has a parameter where you can specify the type of date format that is being used. I don't believe that we have this in DAX
Sep 5, 2016 at 11:15 PM
"When the date argument is a text representation of the date, the day function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", is interpreted as a datetime value equivalent to January 8th of 2009, and the function returns 8. However, if the current date/time settings represent dates in the format of Day/Month/Year, the same string would be interpreted as a datetime value equivalent to August 1st of 2009, and the function returns 1."
https://msdn.microsoft.com/en-us/library/ee634390.aspx

This is how it is supposed to work according to Microsoft, but it doesn't appear to be working this way.
Coordinator
Sep 6, 2016 at 5:41 AM
Edited Sep 6, 2016 at 5:44 AM
Interestingly in SSAS 2016 the culture property of the model in 1200 compatibility mode appears to have an effect on this

So the following query returns 12 and 13.
evaluate 
union( 
    row("Date", "12/1" , "Day", day("12/1/2016")), 
    row("Date", "13/1", "Day" , day("13/1/2016"))
)
If I connect to a model on the same server in 1103 compatibility neither setting the client locale on the connection string or setting the language of the model has any effect and I get back the incorrect results of 1 and 13 from the above query.