Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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-endin...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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)
3861AD Object Deletion2020-12-26T23:59:59.9999999
2328Manual Account Modification2020-12-26T23:59:59.9999999
3158Manual Account Modification2020-12-26T23:59:59.9999999
0275Manual Account Modification2021-01-02T23:59:59.9999999
2648Manual Account Modification2020-12-26T23:59:59.9999999
2970Manual Account Modification2020-11-26T23:59:59.9999999
2864Manual Account Modification2020-11-26T23:59:59.9999999
2667Manual Account Modification2021-01-02T23:59:59.9999999
0161Manual Account Modification2020-12-26T23:59:59.9999999
3472Manual Account Modification2020-12-26T23:59:59.9999999
3048Manual Account Modification2020-12-26T23:59:59.9999999
5674Service Account2020-12-26T23:59:59.9999999
8226Storage2021-01-02T23:59:59.9999999
8233Storage2021-01-02T23:59:59.9999999
8220Storage2021-01-02T23:59:59.9999999
6122Storage2020-12-26T23:59:59.9999999
9452Update Group Scope2020-12-26T23:59:59.9999999
4371Update Group Scope2021-01-02T23:59:59.9999999
9237Update Group Scope2020-12-26T23:59:59.9999999
4401Update Group Scope2021-01-02T23:59:59.9999999
5181Update Group Scope2021-01-02T23:59:59.9999999
8046Update Group Scope2020-11-26T23:59:59.9999999
9129Update Group Scope2020-11-26T23:59:59.9999999
5626Update Group Scope2020-12-26T23:59:59.9999999
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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