Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
you can use a numeric column to sort a Text based column.
Sorter | Fin Year | Month |
2018.12 | 2018 | September |
2019.01 | 2019 | October |
2019.02 | 2019 | November |
2019.03 | 2019 | December |
2019.04 | 2019 | January |
2019.05 | 2019 | February |
2019.06 | 2019 | March |
2019.07 | 2019 | April |
2019.08 | 2019 | May |
2019.09 | 2019 | June |
2019.1 | 2019 | July |
2019.11 | 2019 | August |
2019.12 | 2019 | September |
2020.01 | 2020 | October |
2020.02 | 2020 | November |
2020.03 | 2020 | December |
You select the field, then in the Ribbon go to Modelling>Sort by Column. Select the Sorter column from the list.
you can use a numeric column to sort a Text based column.
Sorter | Fin Year | Month |
2018.12 | 2018 | September |
2019.01 | 2019 | October |
2019.02 | 2019 | November |
2019.03 | 2019 | December |
2019.04 | 2019 | January |
2019.05 | 2019 | February |
2019.06 | 2019 | March |
2019.07 | 2019 | April |
2019.08 | 2019 | May |
2019.09 | 2019 | June |
2019.1 | 2019 | July |
2019.11 | 2019 | August |
2019.12 | 2019 | September |
2020.01 | 2020 | October |
2020.02 | 2020 | November |
2020.03 | 2020 | December |
You select the field, then in the Ribbon go to Modelling>Sort by Column. Select the Sorter column from the list.
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.
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |