Forum Discussion
Date sorting
Hi,
I've posted this quesiton previously, but still can't seem to solve the problem.
I have a list of Facebook posts, which need to be ordered by the post date/time, but it is still ordering it wrong (mar 28 will come after apr 1). I've tried all sorts of sorting methods
Is anyone able to help??
7 Replies
Have you tried going to the data model and setting the field to a date data type?
Alternatively, on the report under modelling you can also set the field to a date data type.
I find that date and locations tend to require explicit modelling for correct sorting and rendering.
Just for fun I downloaded the facebook data for a page I manage. I downloaded both the excel and the csv file.
The date in the files is US format, but I'm Australian and the datetime format of PowerBI uses the local machine settings which in my case is UK format.
The excel document is badly formatted. It can't be opened in PowerBI. I open it in Excel then save as xslt. Excel reformats the dates to local (UK) format and fixes the file format. PowerBI easily imports this new file and date and graphs it correctly.
When I open the csv document in PowerBI I am unable to set the posted column to date/time because PowerBI can't process the US date formats when the local settings are UK.
I suggest taking the date column, splitting it based on the / character then building a new date column by recombining these values. This new column will be in the correct date format and sort correctly.
For reference: https://community.powerbi.com/t5/Desktop/How-to-change-the-date-format/td-p/40460
It may also be that you have the column set to date in the report model but not in the data model. The visualisation might be using the data model rather than the report model settings. I'd try this first.
How about a calendar table consisting of 2 columns: US Date, AU Date
Link the facebook table to the calendar table using the US Date. It will probably be a many:1 relationship.
Use the US date for all calculations but use the AU date in the visualisation. This should allow you to use any date format (or even colours if that's your thing) in the visualisation.
