Forum Discussion

ArtFi's avatar
ArtFi
Frequent Visitor
10 years ago
Solved

Sort months in chronological order

I know this question has been asked a lot, but my specific issue is that I have lot of different data for one chart (which I select with a slicer) and not every year starts with January, eg if it's M...
  • KGrice's avatar
    KGrice
    10 years ago

    Hi ArtFi. Can you combine your desired month number with a year prefix to give it a list of one-to-one values? For example, if you want 2015 to be ordered Jan then Feb etc., they would be numbered 201501, 201502. If you wanted 2016 to be ordered Mar, Jan, Feb, then March would still get 201601, then 201602 for Jan and 201603 for Feb. You'd just need to find some way to define the custom sort order in your column, either through a conditional column or maybe hard-coding the values in an Excel table.

     

    If it's just that a year doesn't start with January because there is no January for that year (e.g., no sales for that month), you could still use the YYYYMM format to sort your months, and if the year starts with March, it won't hurt your chronological sort order anyway.

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

ArtFi

 

I’m a little confused about one thing. If one year starts with May, why you need May to return number 1 in the column next to it?

If you use following column formula for MonthNum, it will return number 5 and you’ll not encounter the problem you have now.

MonthNum =
MONTH ( Table1[Date] )

 

Best Regards,

Herbert

  • ArtFi's avatar
    ArtFi
    Frequent Visitor
    10 years ago

    v-haibl-msft May needs to return 1, because otherwise January of the next year would be 1, February 2 and so on. If I'd try to sort now, January of eg 2016 would before May 2015 in the chart, you see?

    • v-haibl-msft's avatar
      v-haibl-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      ArtFi

       

      As CheenuSing mentioned, you’d better create a calendar table with similar formula as below if you don’t have.

      CalendarTable =
CALENDAR ( "1/1/2015", "12/31/2016" )

      Then create relationship between these two tables with date key.

       

      Create three columns in the Calendar table. Select MonthName column and make it sorted by YearMonth column.

      YearMonth = 
YEAR ( CalendarTable[Date] ) * 100
+ MONTH ( CalendarTable[Date] )
      MonthName = 
FORMAT ( CalendarTable[Date], "mmmm yyyy" )
      Year = 
YEAR ( CalendarTable[Date] )

      Now you should be able to sort months as below. Make sure you select the MonthName column in Calendar table for Axis.

       

      Best Regards,

      Herbert

      • Baskarved's avatar
        Baskarved
        Regular Visitor
        9 years ago

        Hi,

         

        v-haibl-msft , Could you please let us know why are you multiplying the year value in "YearMonth" colum.Since if we change any  number instead of 100 the expected result is not coming.

         

        YearMonth = 
YEAR ( CalendarTable[Date] ) * 100
+ MONTH ( CalendarTable[Date] )

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      ArtFi

       

      Please clarify

       

      a) Do you have any Calendar or Date Table ?

      b) How has this been built- is it continuous from the Minimum of Fact Table date and upto the maximum of Fact Table Date

      c) What is the business rule to be applied to start a new year with different months as the first month number. 

       

      It will be good to  have a sample of your data along with the data model to come out with appropriate solution.

       

      Cheers

       

      CheenuSing