DAX Table Sorting Months

[jmfillman]

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

        )

[Aburar_123]

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,

[jmfillman]

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

[jmfillman]

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

        )

[jmfillman]

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.

[Azadsingh]

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

[v-pgoloju]

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

[jmfillman]

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

[Azadsingh]

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.

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

[techies]

Hi @jmfillman add a column fiscal month number using this 

MONTH(EDATE([Date], 6))

and then sort fiscal month by the above column

[FreemanZ]

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