Calendar w/ months interval

[pierre415]

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?

[enlamparter]

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!

[Rokie]

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.

[StidifordN]

---

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

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]

Where does [Date] in Datecol come from?

[GilbertQ]

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]

[pierre415]

No I was wondering how to create the date table

[v-sihou-msft · Accepted solution]

@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, 

[emiel74]

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

[trt18-sistemas]

If you want to build this table using Power Query (M Language), here is my solution: https://stackoverflow.com/questions/70888522/how-to-create-month-table-in-powe-query-m

[Anonymous]

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.

[vhw04]

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"))

[zgiersky]

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")

[raymond]

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?

[GilbertQ]

Hi @pierre415

Here is a blog post on how to create the Date Table

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...