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, 

     

     

pierre415's avatar
pierre415
Helper I
9 years ago

No I was wondering how to create the date table

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, 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    If you look more closely, you will see that your result is unfortunately wrong for Year-MonthName:

    indeed, year = 2016

    month number = 1

    year-month = 201601

    year-monthname = 2016 Dec

     

    same for all other month, you have Jan for all other month instead of the real expexted value.

     

    unfortunately, I still do not have found a fix for this strange behaviour.

     

    • zgiersky's avatar
      zgiersky
      Frequent Visitor
      7 years ago

      Hi,

       

      Based on your example this custom column worked for me.

       

      date = FORMAT(DATE(LEFT([Year-Month],4),RIGHT([Year-Month],2),1),"yyyy-mm")

    • vhw04's avatar
      vhw04
      New Member
      5 years ago

      I found that if you keep everything that v-sihou-msft had but change the Year - Month Name part of the formula to this, it works correctly: 

      "Year-MonthName",YEAR([Date]) & " " & FORMAT([Date],"MMM"))
    • raymond's avatar
      raymond
      Post Patron
      8 years ago

      I am interest in something similar. 

       

      I would like to create a profit target table on a monthly basis. Hence next to each month I need to place a number that gets generated from a formular. 

       

      Anyone an idea?

  • emiel74's avatar
    emiel74
    New Member
    3 years ago

    Thanks for this solution. I did some bugfixing to generate a jan-dec range. 

    MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDARauto(3),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT([Date],"MMM"),"Year-MonthName",FORMAT([Date],"yyyy MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName], [Month Name])