Forum Discussion

andywil456's avatar
andywil456
Icon for Advocate II rankAdvocate 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
Icon for Advocate II rankAdvocate II
10 years ago

Thanks KHorseman, Works a treat.

 

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity 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"))

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Well not that fast! :smileylol: