Forum Discussion
Month Sorting in a Matrix
Hello,
I have a set of data I want to view comparing results on a month-to-month basis for the last three months.
The rows are the various metrics, the columns are the month of the week-ending date (a datetime type), and there are three values for the middle of the matrix. I basically dropped the field into the column, had the hierarchy displayed, and deleted everything but the month portion.
The results work fine when not crossing a year boundary, however as we now have values for January, columns arrange as January, October, November, December. I understand that it 'makes sense' given that I'm pulling the month field only and January is month 1. However, including the year portion creates another grouping that really isn't needed.
I did go into the data and set the "sort by column" value without a result.
Any suggestions are appreciated; we don't need the year displayed as the visual implies the year as it is a three-month look-back.
Thanks
Hi Anonymous ,
If you don't have duplicated month value in your fact table, you can try the following steps:
1. Create the calendar table:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,10,1),DATE(2021,1,31)),"Year",YEAR([Date]),"MonthNum",MONTH([Date]),"Month",FORMAT([Date],"mmm"),"YEARMONTH",YEAR([Date])*100+MONTH([Date]))2. Sort the month column by YEARMONTH column:
Then it will show like you want:
But if you have duplicated month value in your fact table, you really need to put year in the column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
16 Replies
Hi Anonymous ,
If you don't have duplicated month value in your fact table, you can try the following steps:
1. Create the calendar table:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,10,1),DATE(2021,1,31)),"Year",YEAR([Date]),"MonthNum",MONTH([Date]),"Month",FORMAT([Date],"mmm"),"YEARMONTH",YEAR([Date])*100+MONTH([Date]))2. Sort the month column by YEARMONTH column:
Then it will show like you want:
But if you have duplicated month value in your fact table, you really need to put year in the column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
I must have missed something in your explanation as you are using the data I sent up. I followed, I thought, the instructions you provided and am still getting the results in a non-month order.
1. Created the new calendar table
2. Sorted it by the yearmonth column
3. Created a relationship from the new calendar table to the data table
4. Added a new visual
5. Put the category in the row section, the month from the new calendar table into the column field, and tickets (Count) into the values.
It resulted in the order of Dec, Jan, Nov for the data. Can you post the PBIX so I can see what I didn't do correctly?
Also, "But if you have duplicated month value in your fact table, you really need to put "year" in the column." do you mean that if I did this as an 18-month look back that the year would need to be included in the columns field?Thanks
Hi Anonymous
Sorry, since you are already advanced user (than me), based on the profile, I did not go in details.
I had similar requirement in the past and want to present the data for matrix. so I did like below steps. (for some reasons, my profile do not allow me to upload pbix file)
[I edited this original post reply]
Columns
- MM = Month('Calendar'[Date])
- YYYYMM = Format('Calendar'[Date], "YYYYMM")
- Month = Format('Calendar'[Date], "mmmm")
- Month - 2 = 'Calendar'[Date]
- YYYYMMDD = format('Calendar'[Date], "YYYYMMDD")
Format the "Month - 2" as mmmm and sort the column using YYYYMM
Note: It only works for matrix, which was my requirement too.
The image shows all data, but typically we do rolling 12 months or (12+1) 13 months in matrix.
hint: I created as many format types to the needs in date/calendar table and use it. 🙂
Hope this helps!
Thanks
Can you provide some sample data, a screen shot and the DAX for the measures you're trying?
Hello,
I am not using any dax for values, here is some sample data:
Tickets
(text)Category
(text)Task_Closed_End_Of_Week_dt
(datetime)3861 AD Object Deletion 2020-12-26T23:59:59.9999999 2328 Manual Account Modification 2020-12-26T23:59:59.9999999 3158 Manual Account Modification 2020-12-26T23:59:59.9999999 0275 Manual Account Modification 2021-01-02T23:59:59.9999999 2648 Manual Account Modification 2020-12-26T23:59:59.9999999 2970 Manual Account Modification 2020-11-26T23:59:59.9999999 2864 Manual Account Modification 2020-11-26T23:59:59.9999999 2667 Manual Account Modification 2021-01-02T23:59:59.9999999 0161 Manual Account Modification 2020-12-26T23:59:59.9999999 3472 Manual Account Modification 2020-12-26T23:59:59.9999999 3048 Manual Account Modification 2020-12-26T23:59:59.9999999 5674 Service Account 2020-12-26T23:59:59.9999999 8226 Storage 2021-01-02T23:59:59.9999999 8233 Storage 2021-01-02T23:59:59.9999999 8220 Storage 2021-01-02T23:59:59.9999999 6122 Storage 2020-12-26T23:59:59.9999999 9452 Update Group Scope 2020-12-26T23:59:59.9999999 4371 Update Group Scope 2021-01-02T23:59:59.9999999 9237 Update Group Scope 2020-12-26T23:59:59.9999999 4401 Update Group Scope 2021-01-02T23:59:59.9999999 5181 Update Group Scope 2021-01-02T23:59:59.9999999 8046 Update Group Scope 2020-11-26T23:59:59.9999999 9129 Update Group Scope 2020-11-26T23:59:59.9999999 5626 Update Group Scope 2020-12-26T23:59:59.9999999 Had to do this as 2 different posts, I didn't understand why it kept failing. Anyway, here is a screenshot. As you can see, the month of January goes in front of November unless I also include the year in the column. It may be "as designed" and that's fine as an answer, but I'm hoping to not have to clutter up a visual with a year. I don't need the space for the row as well as I need the entire total not broken down by 'year' as shown in the second visual.
The column value is just pulling the month out of the date hierarchy.
Thanks
Hi Anonymous ,
Would you please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQMGgP4_WNBKlWouIb2JhpkBa6cEPFVUuiCpSId6x9ZTFQ?e=Oun1UX?
And if you have an 18-month look back , for example,2019.6 -2020.12, there are duplicated month value for jan, feb and so on. sort by column will result in error. And we need to use year column to distinguish them.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
