Forum Discussion

SachinC's avatar
SachinC
Helper V
9 years ago

Date Formula for MMM

Hi,

I have a Date field which I broke down using DAX for Day = day({fieldname]), Month = month([fieldname]) and Year = year([fieldname]).  I now want to format my numerical months in my charts to MMM, i.e. Jan, Feb etc... but the problem is:-

 1. I can't seem to convert my Month to MMM using Format([date], "MMM")- any pointers?

 2. How can I get my months in order, i.e. Jan, Feb etc....?

Regards,

S

 

Need Help

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi SachinC

     

    Please try adding these two columns to your table.

     

    MonthName = FORMAT({fieldname},"MMM YY")

MonthID = INT(FORMAT({fieldname},"YYYYMM")

    The first column should be formatted the way you want, but I would use the Sort by column feature to have it sorted by the other column.  

     

    If it were me, I'd be doing this in the Date/Calendar table :)

     

     

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    Hi SachinC,

     

    To sort your month in visual, click on the Modelling tab, and where it says Sort by Column, click on the Drop Down and select the Month Number column you created above.

     

    Regards,

    Charlie Liao

    • SachinC's avatar
      SachinC
      Helper V
      9 years ago

      This is really odd, it doesn't work for me.

      I've followed exactly what you've said, but it's strange I click my new DateID field, click Sort By in the ribbon bar but nothing happens.

      Please help. Thanks.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi SachinC

         

        You have to have the field selected in the menu, or the column selected in the data view,  Then the Sort By Column should offer a dropdown.