Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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?

1 ACCEPTED SOLUTION
Microsoft Employee

@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 15
Helper I

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!

New Member

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.

Helper III

@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
Not applicable

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

Helper I

Where does [Date] in Datecol come from?

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Helper I

No I was wondering how to create the date table

Microsoft Employee

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

New Member

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])``````
Advocate III

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
Not applicable

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.

New Member

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

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

Post Patron

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?

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors