Line chart not showing dates chronologically

[mkwiatkowski]

I Have a line chart that shows days and months, the issue i'm having is i want the chart to show the data chronologically by day/month, right now the chart seems to be ordering by day but rather doing it day-by-day for September and then day-by-day for October its doing it by day number but then sorting months with the same day one after the other. Chart is shown below along with the table:

[v-lid-msft · Accepted solution]

Hi @mkwiatkowski ,

We can use the tooltips field to sort the chart if the x-axis field is based on the column you want to sort, or you have a hierarchy field used as x-axis, just put the date field into the tooltips then set sort as this field

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DouweMeer]

Why are you guys making your lifes more complicated than necessary? And most important, why are you teaching others to work as complicated as well. The problem lies in the fact that the field is considered text. Thus it sorts alphabetical. You have to let Power BI know it is dealing with a date related value. 

ComparisonDate column by formatDate by default hierarchyDate by custom date hierarchy

[mkwiatkowski]

Perfect! thank you, i've now got my chart showing dates chronologically.

[Anonymous]

Here is the blog post:

https://finance-bi.com/power-bi-learn-how-to-custom-sort-a-column/

[DouweMeer]

That method is not an effective trick for this situation. 

[Anonymous]

@DouweMeer :

If the date column is sourcing Dim/Calendar table from database. Date must be is Unique/PK, then

1. In Power Query Editor', Sort Date column

2. Add Index column

3. Close & Apply

4. From Data view create new column as you wanted.

        DaynumerWithMonthName = FORMAT(Hours[Date],"dd,mmm")

5. Now  in the 'modeling' tab, select the new column and select 'Sort by Column' and choose Index column.

Note: If the Date column is not unique from the Source, then select Date column and create new table y selecting 'Add as new Query, then 'Remove Duplictates', THEN convert 'To table'.

[DouweMeer]

Either create a date hierarchy by click and drag the dates in a waterfall on top of each other, or use the 'Sort by column' in the modeling tab on top and use your date column for this purpose.