Month Sorting in a Matrix

[Anonymous]

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

[v-deddai1-msft]

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

[v-deddai1-msft · Accepted solution]

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

[Anonymous]

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

[littlemojopuppy]

Can you provide some sample data, a screen shot and the DAX for the measures you're trying?

[Anonymous]

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

[Anonymous]

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

[littlemojopuppy]

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.

[Anonymous]

Thanks.

I don't want to drop the year into the visualization as it takes up valuable canvas and is inferred by it being the last three months.

Also, it just seems....overkill... to have to create a calendar table to do this simple thing. A calendar table generates all dates based upon the minimum/maximum of the date fields in the model, so I'm generating 3 years of dates for a simple ask. Seems crazy.

With this video from Guy In a Cube https://www.youtube.com/watch?v=2f7dYB1l84g he shows how to do month-to-month comparisons as well without needing to create that overhead in the model. *shrug*

I've done my best to replicate your suggestion - however, it does not seem to order by the month (Nov, Dec, Jan) without me including the year in the column. Maybe I'm missing something obvious from your suggestion, so I've included a link to my PBIX on my google drive.

Simple PBIX with Data 

Thanks

[sevenhills]

https://databear.com/power-bi-tips-sort-by-month-name/

[Anonymous]

I don't know what you are attempting to show here. I'm not having an issue with sorting by the month name unless it's your assertion that I create a measure that combines the 4 digit year preceding the month name.

[sevenhills]

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

[Anonymous]

Thanks - yeah, I have done some advanced things in the past, but I still have gaps in my knowledge you can drive a semi-truck through. I appreciate the more detailed answer so I could see your thought process and how it applied to the post you linked.

I tried that and didn't have any luck, I think it's because I'm using a matrix visualization as opposed to a table or other visual - they seem to react differently.

[sevenhills]

give me two minutes, my explanation looks wrong ... will update by looking my pbix file

[sevenhills]

Hi @Anonymous ,  I updated my original reply above, please check

[sevenhills]

Hi @Anonymous 

I download your PBIX file.

a) Modified your calendar table as

Calendar = ADDCOLUMNS ( CALENDARAUTO (),
    "Year", FORMAT([Date], "yyyy"), 
    "MonthNo", MONTH([Date]), 
    "Month", FORMAT([Date],"MMM"),
    "Quarter", FORMAT([Date],"\QQ"),
    "YearMonth", FORMAT([Date],"YYYY-MM"), 
    "WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
    "Weekday", FORMAT([Date],"ddd"), 
    "WeekNo", WEEKNUM([Date], 2),
    "Month - 2", [Date]
 )

b) Select "Month - 2" and clicked Sort by Column as "YearMonth"

c) matrix modified to use "Month -2" and it works!

Thanks