Forum Discussion
Calendar w/ months interval
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,
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,
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.
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")
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
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])
