Forum Discussion

pierre415's avatar
pierre415
Helper I
9 years ago
Solved

Calendar w/ months interval

I need to build a date table with months as intervals instead of days, as in - Jan 2017 Feb 2017 March 2017 . . . (today's month/year)     I tried using List.Dates() but I think the biggest...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    pierre415

     

    I assuem you assign start date and end date. Then you can create a calculated table with DAX. Please refer to my sample below:

     

    MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

     

    Regards, 

     

     

enlamparter's avatar
enlamparter
Helper I
7 years ago

I know this question has been closed for quite a while, but for any future viewers who are struggling to make the accepted solution work, there is one tiny adjustment to make. 

 

FORMAT(MONTH([Date]),"MMM")

needs to be...

FORMAT([Date],"MMM")

 

Additionally, you can omit the expression for Month Name within the addcolumns expression. It isn't needed. The complete, corrected code is below:

 

monthTable = var FullCalendar = ADDCOLUMNS(CALENDAR("2017/1/1",today()),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMM"),6),"Year-MonthName",YEAR([Date]) & " " & Format([Date],"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

Good luck!

  • StidifordN's avatar
    StidifordN
    Helper III
    7 years ago
    enlamparter wrote:

    I know this question has been closed for quite a while, but for any future viewers who are struggling to make the accepted solution work, there is one tiny adjustment to make. 

     

    FORMAT(MONTH([Date]),"MMM")

    needs to be...

    FORMAT([Date],"MMM")

     

    Additionally, you can omit the expression for Month Name within the addcolumns expression. It isn't needed. The complete, corrected code is below:

     

    monthTable = var FullCalendar = ADDCOLUMNS(CALENDAR("2017/1/1",today()),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMM"),6),"Year-MonthName",YEAR([Date]) & " " & Format([Date],"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

    Good luck!

    Perfect.  This should be the quoted solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is the correct solution! Format( [MonthKey], "MMM" ) was giving me correct values but shifted by 1. So "1" was showing December, "2" January and so on. Using Date instead of MonthKey fixed the issues. Kudos to you.