Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Monthly Calendar DAX (Month name showing only January)

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                                 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. 
 
 
2 ACCEPTED SOLUTIONS
marceloVVR
Helper I
Helper I

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

View solution in original post

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Solution already provided above.

 

1.jpg

 


Regards,

Harsh Nathani


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

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

View solution in original post

5 REPLIES 5
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Solution already provided above.

 

1.jpg

 


Regards,

Harsh Nathani


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

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

Anonymous
Not applicable

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

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
AntrikshSharma
Super User
Super User

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
Helper I
Helper I

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.