Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |