cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pierre415
Helper I
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

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

234.PNG

 

Regards, 

 

 

View solution in original post

14 REPLIES 14
enlamparter
Helper I
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!

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.


@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

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

Where does [Date] in Datecol come from?

GilbertQ
Super User
Super User

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]




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

Proud to be a Super User!







Power BI Blog

No I was wondering how to create the date table

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

234.PNG

 

Regards, 

 

 

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


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.

 

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

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?

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





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

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors