Forum Discussion

andywil456's avatar
andywil456
Advocate II
10 years ago
Solved

formating month

I have created a calendar table, but when i create a month column with a format, it only gives months of January or December.  For example i'm getting Feb to June dates returning a month value of Jan...
  • Anonymous's avatar
    Anonymous
    10 years ago

    You don't need to nest any date component formulas inside a FORMAT formula in order to get a single component like month. FORMAT(DateTable[Date], "MMM") should give you what you're looking for.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

You don't need to nest any date component formulas inside a FORMAT formula in order to get a single component like month. FORMAT(DateTable[Date], "MMM") should give you what you're looking for.

  • andywil456's avatar
    andywil456
    Advocate II
    10 years ago

    Thanks KHorseman, Works a treat.

     

    • Sean's avatar
      Sean
      Community Champion
      10 years ago

      Anonymous is lightning fast - so I'll just post a slightly more readable version of andywil456's formula

       

      EDIT: Now with MONTHSORT column

       

      Date =
ADDCOLUMNS (
    CALENDAR ( "01-Jan-2015", "31-12-2017" ),
    "DATEASINTEGER", FORMAT ( [Date], "DD/MM/YY" ),
    "Year", YEAR ( [Date] ),
    "MONTH & YEAR", FORMAT ( [Date], "MMM YYYY" ),
    "EOMONTH", FORMAT ( EOMONTH ( [Date], 0 ), "MMMM" ),
    "END OF MONTH", FORMAT ( EOMONTH ( [Date], 0 ), "MMMM/YYYY" ),
    "MONTH", FORMAT ( [Date], "MMM" ),
    "MONTHSORT", INT ( FORMAT ( [Date], "yyyyMM" ) )
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

         

        Sean very good. While you're at it might as well add a sort order for the Month & Year column

         

        "MonthSort", INT(FORMAT([Date], "yyyyMM"))