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, 

     

     

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

  • pierre415's avatar
    pierre415
    Helper I
    9 years ago

    Where does [Date] in Datecol come from?