Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Custom Order of Fiscal Year Months in Matrix

Hello,

 

I'm trying to build my Matrix so it shows the Fiscal Year Months in the correct order based on Fiscal Year starting on October. Currently, I'm trying to show the last 13 months in the Matrix but came across a problem. As you can see in the below 1st screenshot, the FY starts in October and ends in September. Now, I want my Matrix to show the same ordering of Year and Month which is currently only looking at the past 12 Months using a column to Filter on(including the current Month) that can be seen in the 2nd screenshot below. 

 

The issue is when I change my logic in the column I'm filtering the 12 Months on to now show 13 Months, the Month of December now appears underneath 2019 to the left of September that can be see in the 3rd screenshot below. I want December to appear on the far left-hand side before January as that's how the Fiscal Year behaves where December is the 3rd Fiscal Month of the Fiscal Year as seen in the 1st screenshot below. Is there a way to achieve the desired result in a Matrix?

 

TableScreenshot-FiscalYearMonthOrder.PNG

 

MatrixScreenshot-FYandMonthOrder.PNG

 

MatrixScreenshot-FYandMonthOrderDEC.PNG

1 ACCEPTED SOLUTION
nhoward
Resolver I
Resolver I

you can use a numeric column to sort a Text based column.  

 

SorterFin YearMonth
2018.122018September
2019.012019October
2019.022019November
2019.032019December
2019.042019January
2019.052019February
2019.062019March
2019.072019April
2019.082019May
2019.092019June
2019.12019July
2019.112019August
2019.122019September
2020.012020October
2020.022020November
2020.032020December

 

You select the field, then in the Ribbon go to Modelling>Sort by Column.   Select the Sorter column from the list. 

 

 

image.png

View solution in original post

3 REPLIES 3
nhoward
Resolver I
Resolver I

you can use a numeric column to sort a Text based column.  

 

SorterFin YearMonth
2018.122018September
2019.012019October
2019.022019November
2019.032019December
2019.042019January
2019.052019February
2019.062019March
2019.072019April
2019.082019May
2019.092019June
2019.12019July
2019.112019August
2019.122019September
2020.012020October
2020.022020November
2020.032020December

 

You select the field, then in the Ribbon go to Modelling>Sort by Column.   Select the Sorter column from the list. 

 

 

image.png

Anonymous
Not applicable

I was using a Date Hierarchy field as my Column Headers to show Year and Month but couldn't get Fiscal Months in the correct order. This was the issue as I had to use a text-based column that you mentioned @nhoward. I missed this key piece in your post when I read it initially.

Anonymous
Not applicable

I searched online and tried different possible solutions including what you mention here and still can't get it to work in a Matrix visual. I tried sorting on Fiscal Year and Fiscal Month in Power Query M and then added an Index to then sort columns on in the Data Pane. No luck here. Seems like the Matrix visual itself might be the cause of the problem as I can get the Table visual to order the Fiscal Months correctly. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.