topic Re: DAX Table Sorting Months in DAX Commands and Tips

DAX Table Sorting Months

jmfillman — Fri, 25 Jul 2025 01:51:29 GMT

I am creating a date table with the below, but whenever I drop this into a Matrix displaying columns of Fiscal Year, Quarter, and Month, the Months are in alphabetic order by quarter instead of month order and I cannot figure out how to get the Matrix to display in the correct order.

Date Calendar =

VAR _today = TODAY()

VAR _quarterStart = DATE ( YEAR ( _today ), ROUNDUP ( DIVIDE ( MONTH ( _today ) -1, 3 ), 0 ) * 3 - 2, 1 )

VAR _quarterEnd = EOMONTH(_quarterStart, 17)

VAR _calendar = CALENDAR(_quarterStart, _quarterEnd )

RETURN

ADDCOLUMNS(

_calendar,

"Sort Column", FORMAT( [Date] , "Fixed"),

"Fiscal Year", YEAR( EDATE([Date],6) ),

"Fiscal Month", FORMAT( [Date] , "mmm"),

"Fiscal Quarter", "Q" & QUARTER( EDATE([Date],6) )

)

Re: DAX Table Sorting Months

FreemanZ — Fri, 25 Jul 2025 02:17:55 GMT

hi jmfillman,

you can sort one column by another column.

try to

1) add a mm column in your calendar table like:

Date Calendar = VAR _today = TODAY() VAR _quarterStart = DATE ( YEAR ( _today ), ROUNDUP ( DIVIDE ( MONTH ( _today ) -1, 3 ), 0 ) * 3 - 2, 1 ) VAR _quarterEnd = EOMONTH(_quarterStart, 17) VAR _calendar = CALENDAR(_quarterStart, _quarterEnd ) RETURN ADDCOLUMNS( _calendar, "Sort Column", FORMAT( [Date] , "Fixed"), "Fiscal Year", YEAR( EDATE([Date],6) ), "Fiscal Month", FORMAT( [Date] , "mmm"), "Month", FORMAT( [Date] , "mm"), "Fiscal Quarter", "Q" & QUARTER( EDATE([Date],6) ) )

2) sort the fiscal month column by month column

more about how:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

Re: DAX Table Sorting Months

techies — Fri, 25 Jul 2025 03:43:08 GMT

Hi jmfillman add a column fiscal month number using this

MONTH(EDATE([Date], 6))

and then sort fiscal month by the above column

Re: DAX Table Sorting Months

jmfillman — Sat, 26 Jul 2025 00:27:02 GMT

I appreciate the responses. Setting the sort column in the data view has no impact on the order in the Matrix. The sort in the below image is correct...

but the Matrix continues to show the months in alphabetical order

Re: DAX Table Sorting Months

Anonymous — Mon, 28 Jul 2025 07:17:24 GMT

Hi jmfillman,

If the issue still persists, we kindly request you to share the sample data in a workable format such as text, an Excel file, or a PBIX file with sample data instead of screenshots. Additionally, please include the expected output. This will enable us to assist you more effectively.

Thanks & Regards,

Prasanna Kumar

Re: DAX Table Sorting Months

Azadsingh — Mon, 28 Jul 2025 10:57:16 GMT

hi jmfillman , Please create a sort column in your table and then use sort by column and use the new column "MonthSort =
SWITCH(
[MonthName],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12,
BLANK()
)"

Re: DAX Table Sorting Months

jmfillman — Mon, 28 Jul 2025 21:17:36 GMT

I would like to add a PBIX files here, but Fabric doesn't seem to allow it, so sharing a link to a sample PBIX file: Matrix Calendar Display

Adding a numeric month value and setting that as the sort column had no impact on the month order displayed in the matrix. The table view sorts as expected, but that has no impact on the matrix month order of display.

Re: DAX Table Sorting Months

jmfillman — Mon, 28 Jul 2025 23:52:48 GMT

I am not able to upload a PBIX file for some reason. Hoping images showing the setup of the matrix (columns only so far) will help.

I added a numeric Month column to the table. Filtering on the month column (or any column) makes no difference to what is diplaying on the matrix.

Date Calendar =

VAR _today = TODAY()

VAR _quarterStart = DATE ( YEAR ( _today ), ROUNDUP ( DIVIDE ( MONTH ( _today ) -1, 3 ), 0 ) * 3 - 2, 1 )

VAR _quarterEnd = EOMONTH(_quarterStart, 17)

VAR _calendar = CALENDAR(_quarterStart, _quarterEnd )

RETURN

ADDCOLUMNS(

_calendar,

"Sort Column", FORMAT( [Date] , "Fixed"),

"Fiscal Year", YEAR( EDATE([Date],6) ),

"Fiscal Month", FORMAT( [Date] , "mmm"),

"Month", FORMAT( [Date] , "mm"),

"Fiscal Quarter", "Q" & QUARTER( EDATE([Date],6) )

)

Re: DAX Table Sorting Months

Azadsingh — Tue, 29 Jul 2025 13:01:37 GMT

Hi jmfillman , Please modify your formula and create a extra column in table. So far what i understood i you need to sort the months my April, May, June and so on for every quarter.

RETURN
ADDCOLUMNS(
_calendar,
"Sort Column", FORMAT( [Date] , "Fixed"),
"Fiscal Year", YEAR( EDATE([Date],6) ),
"Fiscal Month", FORMAT( [Date] , "mmm"),
"Month", FORMAT( [Date] , "mm"),
"Fiscal Quarter", "Q" & QUARTER( EDATE([Date],6) ),
"FiscalMonthNumber",
SWITCH(
MONTH(EDATE([Date],6)),
1, 10,
2, 11,
3, 12,
4, 1,
5, 2,
6, 3,
7, 4,
8, 5,
9, 6,
10, 7,
11, 8,
12, 9
)
)

Re: DAX Table Sorting Months

jmfillman — Tue, 29 Jul 2025 19:56:24 GMT

HI Azadsingh, techies , and FreemanZ

I copied in updated formula suggestions and set the sort to the various columns suggestions, but does change to the month sorting. For the purposes of demonstration, I added the FiscalMonthNumber to the matrix. Sorting on this column, like any of the others, does not change the sort order in the matrix. You can see this in the PBIX file I linked to in a previous post here: Calendar Matrix

This information provided here: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column seems to only apply to gaph visuals, not the matrix visual. Following the first set of steps, to set the Sort Column, as noted here and previously, does not change the sort order of my matrix. Maybe a bug in the July BI Desktop release??

If I remove the Fiscal Month column and just keep the month number, the numeric month order is correct, but choosing the sort column has no impact on how the matrix is sorting, it is always going in a-z/1-9 order depending on the data type, no matter what sort column is selected.

To confirm, I need to display like the below:

2025 2026

Q4 Q1 Q2 Q3
Apr May Jun July Aug Sep Oct Nov Dec Jan Feb Mar

Re: DAX Table Sorting Months

Aburar_123 — Wed, 30 Jul 2025 03:10:45 GMT

Hi jmfillman ,

it is working. i just take your date table script and sort the date field by the sort column option and i can make the month in order. please follow the below options,

Re: DAX Table Sorting Months

Anonymous — Mon, 04 Aug 2025 04:36:16 GMT

Hi jmfillman,

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

Re: DAX Table Sorting Months

jmfillman — Mon, 04 Aug 2025 06:31:51 GMT

Hi Anonymous,

No, still not working. At this point, my only guess it is something with the July BI Desktop release.

I've linked to a video of me selecting several differenct columns to sort by which shows nothing changes in the matrix.

Short video: Sort Columns Not Sorting

Re: DAX Table Sorting Months

Anonymous — Thu, 07 Aug 2025 05:38:10 GMT

Hi jmfillman,

If the issue still persists, I’d recommend raising a support ticket with Microsoft. The support team can look into the backend and provide more in-depth assistance tailored to your environment.
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

Thanks & Regards,

Prasanna Kumar

Re: DAX Table Sorting Months

Anonymous — Wed, 13 Aug 2025 05:34:44 GMT

Hi jmfillman,

Just checking, have you had a chance to open a support ticket, as suggested. If so, we'd love to hear the current status or any updates from that.

Regards,

Prasanna Kumar

Re: DAX Table Sorting Months

jmfillman — Mon, 18 Aug 2025 00:53:43 GMT

Myself and my team were cut and no longer working on what this was ultimately a part of.