Forum Discussion
Month Sorting in a Matrix
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
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 ...you're not trying to calculate month on month comparison (which is what I originally interpreted your request as). You're literally just trying to get January 2021 to appear after December 2020, right? You basically want this is your output, right???
Do you have a date table and is it marked appropriately? If not, create one. Easiest way is to use the CALENDARAUTO function. You can add columns for year, quarter, month, etc. Check the "Date and Time Functions" listed here. If you want to create MonthName or WeekdayName fields the easiest way is like this: FORMAT(Calendar[Date], "MMMM") for month name and substitute "DDDD" for weekday. Once you add those, if you created MonthName and WeekdayName fields, you should sort them by the appropriate number fields you created. But your goal is to have a date table that looks like this.
After you do that, you can either create a hierarchy in the date table for year/month or just drop year, month, etc. into the field well for the visualizations (year first, then month, etc). Your visualization should end up like the first pic.