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 interval you can have is a day. Any advice?

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, 

     

     

15 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi pierre415

     

    Try this

     

    MonthAndYears =

    Var Datecol = SELECTCOLUMNS(CALENDAR(TODAY()-365,TODAY()),"DateAdded",[Date])
    Var MonthCol = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(Datecol,"MonthName",FORMAT([DateAdded],"MMMM-YYYY")),"MonthNames",[MonthName]))

    Return MonthCol

     

    The above formula in one line::

     

    MonthAndYears = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(CALENDAR(TODAY()-365,TODAY()),"DateAdded",[Date]),"MonName",FORMAT([DateAdded],"MMMM-YYYY")),"MonNameSel",[MonName]))

     

     

    You can adjust how dates are returned in the first step. I just used 365days back from today.

     

    Let me know if it addresses your requirement

     

    Thanks

    Rup

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi pierre415

     

    If you have your date table already setup, in the Query Editor you could create a new Custom Column which would have the following syntax if your Month Column is called "Month" and your year column is called "Year"

     

    [Month] & " " & [Year]
      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        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,