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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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