cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Months in wrong order

Hi!

 

I’m trying to order months in the right order in a table and in a graph. From what I understand, I have to order by month number in my “Date” Table, but it doesn’t work. What I am doing wrong?

 

I created my table using the following DAX code I found online :

 

 

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

 

 

And the table looks like this :

 

 

Capture d’écran 2018-08-23 à 17.04.26.png

 

(My computer is in French, so the months names are displayed in French)

 

I did a sort on the monthnumber, but nothing change.

 

Here are the two problems I’m trying to solve:

 

First, I have a matrix displaying months as columns, but the order is not right

 

I get something like this :

 

 

 

april

february

January

july

june

Area 1

231

54

851

1651

814

Area 2

236

2321

568

6546

65

Area 3

651

651

2651

2136

6516

 

It looks like the months appears in alphabetical order, even if the Time table is ordered by month number.

 

 

The second problem is about the same in a graph, but now the months are not displayed in alphabetical order. Here's a screenshot :

Capture d’écran 2018-08-23 à 17.04.15.png

 

 

 

 

How can I solve this to get the months in the right order since ordering by monthnumber doesn't seem to work for me ? What I am doing wrong? My data table is linked to the Date table. My data table has date format as YYYY-MM-DD.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Months in wrong order

Hi @PatP

I think your monthnumber column is text field instead of number. You can check once.

View solution in original post

9 REPLIES 9
Highlighted
Solution Specialist
Solution Specialist

Re: Months in wrong order

Are you, by chance, trying to solve this by sorting the month number column, itself?  If so, that will not work.  You need to select the Month Name column, and then choose the "Sort by Column" option.  From there, choose to sort by the month number column.

 

Sort By.png

 

That will turn your chart from looking like this:

Start.png

 

To this:

end.png

 

 

Highlighted
Frequent Visitor

Re: Months in wrong order

This is exactly what I did, but the order in the table and chart remain the same. What else could explain this?

Capture d’écran 2018-08-24 à 18.00.38.png

 

 

 

Highlighted
Super User IV
Super User IV

Re: Months in wrong order

Hi,

 

In that Table, ensure that you keep your active cell in the Month column and then go to Sort by Column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Months in wrong order

Hi,

Thanks for your reply. This is what I did that you can see in my last screenshot I think. Is that what you mean or something else that I'm missing?
Highlighted
Frequent Visitor

Re: Months in wrong order

Hi @PatP

I think your monthnumber column is text field instead of number. You can check once.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Months in wrong order

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Months in wrong order

@rema you are right! But even if I change if as a number, it doesn't work! I should be missing something 😕

 

Highlighted
Frequent Visitor

Re: Months in wrong order

I finally got it to work! I wasn't sorting the right column. Beginner mistake. Thanks!

Highlighted
Anonymous
Not applicable

Re: Months in wrong order

Buenas tardes, Estimada Comunidad

tengo un caso similar, me podrian ayudar por favor;

como aplicaria en esta funcion DAX, el order by, es que quisiera visualizar los meses en orden es decir, Enero, Febrero...etc, me sale en desorden, adjunto codigo de mi funcion, por favor su ayuda:

ResumenConceptoXMes2019 = SUMMARIZECOLUMNS (     Fact_Venta[MesFechaEmision],  Dim_BLConcepto[Concepto],     FILTER ( Fact_Venta, Fact_Venta[AñoFechaEmision] = 2019 ),     "Total Ventas", SUM (Fact_Venta[ImporteTotal]) )

 

De antemano muchas gracias; 

Adjunto captura de pantalla del resultado:

ee.PNG

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors