Date sorting

[Anonymous]

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??

[Ormesome]

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.

[Ormesome]

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.

[Anonymous]

Hi Ormesome,

Yes, it's formatted to be date/time, so I'm not sure why it's not sorting correctly ?

Any other ideas?

Thanks,
Sonia

[Ormesome]

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.

[Anonymous]

Hi @Ormesome, 

Thanks for your help! 

The main date column in my post data is formatted as 'mm dd yyyy' (this is how it is in the excel) and my power bi locale settings are US.

Perhaps the problem is that I've formatted this date manually into a new column 'Date AU' and used this on the graphs? (Date AU = format('POST DATA'[Date],"dd mmm hh:mm")). When I go to the query editor, this column isn't there as it's not from the original data set. 

Is there another way around this? If I pull in the 'date' column onto my graph, it brings up the date heirachy, but this doesn't include 'time', which i need for the graph. 

Hopefully this all makes sense, thank you for your help!

Sonia

[Ormesome]

In the data model try setting the new AU column to sort by the original US column.

[Anonymous]

hi @Ormesome, 

I get this error, unfortunately 😞

I can't seem to sort it by anything...