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

 

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

5 Replies

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

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