formating month

[andywil456]

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 January.

The code i'm using is 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(MONTH([Date]),"MMM"))

[KHorseman · Accepted solution]

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]

Thanks KHorseman, Works a treat.

[Sean]

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

[KHorseman]

@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]

Well not that fast!