topic Re: Monthly Calendar DAX (Month name showing only January) in DAX Commands and Tips

Monthly Calendar DAX (Month name showing only January)

Anonymous — Mon, 13 Jul 2020 21:15:33 GMT

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.

Re: Monthly Calendar DAX (Month name showing only January)

marceloVVR — Tue, 14 Jul 2020 00:19:27 GMT

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

Re: Monthly Calendar DAX (Month name showing only January)

AntrikshSharma — Tue, 14 Jul 2020 04:44:01 GMT

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

Re: Monthly Calendar DAX (Month name showing only January)

amitchandak — Tue, 14 Jul 2020 05:04:57 GMT

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

Re: Monthly Calendar DAX (Month name showing only January)

Anonymous — Tue, 14 Jul 2020 06:59:37 GMT

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!

Re: Monthly Calendar DAX (Month name showing only January)

Anonymous — Tue, 14 Jul 2020 09:13:41 GMT

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