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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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

15 REPLIES 15
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, 

 

 

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

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.

 

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

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.