Dates stopped sorting in order

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

Did it

Same thing on visual:

Chart remains the same 😕

[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.

[Anonymous]

Gabriel, what DAX formula to use in order co create sorting columns with month and years as numbers?

Jan 2020 is 1
Feb 2020 is 2 and so on

I read some article in which you have to create new table, but can we do it without new table?

[Gabriel_Walkman · Accepted solution]

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")

[Anonymous]

Wow, after resfreshing it seems like things are working correctly.

Big thanks Gabriel_Walkman for the answer!

BA_Pete- thanks for your contribution ❤️

[BA_Pete]

Hi @Anonymous ,

Very glad to hear it's working for you, but disappointed that you didn't accept my answer as the solution. Could you let me know what the answer that you accepted gave you that mine didn't please? I think the solution provider even mentioned the fact that they had just used my answer to provide theirs.

Thanks in anticipation,

Pete

[Anonymous]

I couldn't mark your answer as a solution because this configuration "mmm" "yy" did not work (like on attached screenshots)

Gabriel only modified it to "yy" "mm" so all is numeric- then it starts working. 

I could experiment with transforming code by myself and mark your answer as a solution, but Gabriel modified your answer and gave me everything ready.

Next time gonna be more carefull about it.

[BA_Pete]

Hi @Anonymous ,

Thanks for getting back to me, I appreciate you taking the time to explain.

I see what the issue was: you wanted to use the [yearMonth] column as a sort column for your [Month] column so you could use [Month] in the axis, in which case Gabriel's numerical version was indeed correct. In my answer, I expected you to use the [yearMonth] column as the axis, not as a sort column.

I'll pay more attention going forward 🙂

Pete

[BA_Pete]

Hi @Anonymous ,

Silly question, I'm sure, but have you checked that your chart sorting hasn't been reset to default somehow?

Pete

[Anonymous]

Thanks for your answer!

Yes, I have been trying to do so, trying to google this for an trying different things and options.

Still not right.

 For days, it is working well:

Btw. month are formatted as date as well as days.

[Gabriel_Walkman]

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?

[Anonymous]

Thanks for your answer!

In a table, everything looks well, I can sort easily by date.

Funny thing is, that for days it works fine, like here

[Anonymous]

I did try this, seems like it doesnt work as well.