Monthly Calendar DAX (Month name showing only January)

[Anonymous]

Hi All, 

I'm having some trouble with a bit of DAX code. 

What I would like to do is generate a calendar whether the Months are the most detailed level. 

Currently my code looks like this: 

MonthTable =

var varCalendar =

ADDCOLUMNS(

CALENDAR("2000/1/1","2030/12/31"),

"Year", year([Date]),

"Year-Month", YEAR([Date])&"-"&format(MONTH([Date]),"00"),

"Month Number", Month([Date]),

"Month Name", format(month([Date]),"mmmm"))

return

//DISTINCT(CALENDAR

SUMMARIZE(varCalendar, [Year],[Year-Month],[Month Number], [Month Name])

The goal is to get a table as follows: 

Year   YearMonth   Month Number   Month Name

2019  2019-12       12                         December

2020  2020-01        1                          January

2020  2020-02        2                          February

2020  2020-03        3                          March

However, the result my code gives is as follows: 

Year   YearMonth   Month Number   Month Name

2019  2019-12       12                         January

2020  2020-02        2                          January

2020  2020-03        3                          January

2020  2020-04        4                          January

So the month number 1 is completely skipped, and the month name is January everywhere. 

The problem seems to be in the Month Name column, since when I leave this out of the Summarize function at the end, the month numbers do go from 1 to 12. 

Any thoughts on what I might be doing wrong here? 

Thanks in advance for any suggestions. 

[marceloVVR · Accepted solution]

try like that

Just change "Month Name", FORMAT([Date],"mmmm").

MonthTable =

var varCalendar =

ADDCOLUMNS(

CALENDAR("2000/1/1","2030/12/31"),

"Year", year([Date]),

"Year-Month", YEAR([Date])&"-"&format(MONTH([Date]),"00"),

"Month Number", Month([Date]),

"Month Name", FORMAT([Date],"mmmm"))

return

//DISTINCT(CALENDAR

SUMMARIZE(varCalendar, [Year],[Year-Month],[Month Number], [Month Name])

Hope this helps

[harshnathani · Accepted solution]

Hi @Anonymous ,

Solution already provided above.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you for this. This did the trick. 
Any Idea why the Month() for the monthname caused an issue? 

[amitchandak]

@Anonymous , I corrected the format please check

MonthTable =
var varCalendar =
ADDCOLUMNS(
CALENDAR("2000/1/1","2030/12/31"),
"Year", year([Date]),
"Year-Month", format([Date],"YYYY-MM"),
"Month Number", Month([Date]),
"Month Name", format([Date],"mmmm"))
return
//DISTINCT(CALENDAR
SUMMARIZE(varCalendar, [Year],[Year-Month],[Month Number], [Month Name])

[AntrikshSharma]

Try this:

Table =
VAR MinDate =
    YEAR ( MIN ( Sales[Order Date] ) ) //Replace Sales with any table with dates
VAR MaxDate =
    YEAR ( MAX ( Sales[Order Date] ) ) //Replace Sales with any table with dates
VAR CalendarStartDate =
    DATE ( MinDate, 1, 1 )
VAR CalendarEndDate =
    DATE ( MaxDate, 12, 31 )
VAR DateList =
    CALENDAR ( CalendarStartDate, CalendarEndDate )
VAR Result =
    GENERATE (
        DateList,
        VAR StartDate = [Date]
        RETURN
            ROW (
                "MonthNumber", MONTH ( StartDate ),
                "Month Name", FORMAT ( StartDate, "MMMM" ),
                "Year", YEAR ( StartDate ),
                "Year Month", FORMAT ( StartDate, "YYYY MM" )
            )
    )
RETURN
    SUMMARIZE ( Result, [Year], [Year Month], [Month Name], [MonthNumber] )

[marceloVVR · Accepted solution]

try like that

Just change "Month Name", FORMAT([Date],"mmmm").

MonthTable =

var varCalendar =

ADDCOLUMNS(

CALENDAR("2000/1/1","2030/12/31"),

"Year", year([Date]),

"Year-Month", YEAR([Date])&"-"&format(MONTH([Date]),"00"),

"Month Number", Month([Date]),

"Month Name", FORMAT([Date],"mmmm"))

return

//DISTINCT(CALENDAR

SUMMARIZE(varCalendar, [Year],[Year-Month],[Month Number], [Month Name])

Hope this helps