March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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?
Solved! Go to Solution.
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,
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
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?
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]
No I was wondering how to create the date table
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,
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
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:
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |