Forum Discussion
Dates stopped sorting in order
Hello,
Today, when working on report everything is going well.
Suddenly things stopped work well:
My date charts got crazy, they stopped to show up in order.
(styczeń- january, listopad- november, grudzień- december)
I did open another powerBi file, everything going well there.
It is happening with every chart that I am creating in this file.
I've only ever modified my Calendar table in power query, but I'll give it a try by editing BA_Pete's answer for a dax calculated column:
yearMonth = FORMAT([date], "yy") & "-" & FORMAT([date], "MM")
15 Replies
Gabriel_WalkmanContinued Contributor
Create an index for the months if it doesn't exist already, then choose your month parameter and go to Column tools >Sort by Columns > choose the index.
Or is there something else wrong with the charts too?
- BA_Pete
Super User
Hi Anonymous ,
Silly question, I'm sure, but have you checked that your chart sorting hasn't been reset to default somehow?
Pete
I know why this had happened.
Until 2020, everything was in order.
PowerBi is connected with excel file, where requests from google forrms are submitted
Yesterday new request came in from from 07.01.2021, and now powerBi takes this January and put it before November, ignoring that year has changed.
Do you know how can I put the fresh dates first?
It is happening with every chart type- BA_Pete
Hi Anonymous ,
If you want January 2021 to show after (further right on axis than) November and December 2020 you will need to give the chart visuals some concept of year. It appears as though you are currently using only Month on your axis, in which case they are being sorted correctly - January is indeed before November and December.
I would recommend creating a new column, either in your data source or in a calendar table (best practice), for Month/Year and using this on your axes. This will allow your chart to understand that Jan 2021 comes after Nov/Dec 2020.
Assuming the dates in your data source are complete dates, i.e. a day, a month, and a year component, then you can create your new column as follows:
DAX
monthYear = FORMAT([date], "MMM") & " " & FORMAT([date], "yy")Power Query M
monthYear = Text.Combine({Text.Start(Date.MonthName([date]), 3), Text.End(Text.From(Date.Year([date])),2)}, " ")Pete
- Gabriel_Walkman
When I create a column for month names, I include the year too, whenever the date in question is not in the current year. Ie. december of last year would display as "Dec 2020" and january from this year would be just "Jan".
I'd create a separate sorting column for the months that always include year and month as numbers. Then choose your actual month parameter and go to Column tools >Sort by Columns > choose the sorting column.
