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.

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"))

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    Well not that fast! :smileylol: