Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
(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 :
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.
Solved! Go to Solution.
Hi @PatP
I think your monthnumber column is text field instead of number. You can check once.
Hello,
I am having the same issue. I tried everything. My month name is getting sorted by Month Number (which is a whole number). I am using a shared dataset. If I put the visual in shared dataset to test, it gives correct value. But in report itself which is referencing to the Shared data set gives the wrong sort order. Not sure why it's happening. It was working fine earlier.
In Report:
In Shared Dataset: Correct sort 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:
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.
That will turn your chart from looking like this:
To this:
This is exactly what I did, but the order in the table and chart remain the same. What else could explain this?
Hi,
In that Table, ensure that you keep your active cell in the Month column and then go to Sort by Column.
Hi,
Share the link from where i can download your PBI file.
Hi @PatP
I think your monthnumber column is text field instead of number. You can check once.
@rema you are right! But even if I change if as a number, it doesn't work! I should be missing something 😕
I finally got it to work! I wasn't sorting the right column. Beginner mistake. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |