Forum Discussion
Build calendar using year
Hello, I'm trying to build a calendar where the min date starts 20 years ago from this year, and max date is 5 years ago from this year.
So because I'm 2024:
The first date is:
01/01/2004
The last date is
31/12/2019
Thank you
CalendarTable =
VAR MinDate = DATE(YEAR(TODAY()) - 20, 1, 1)
VAR MaxDate = DATE(YEAR(TODAY()) - 5, 12, 31)
RETURN
ADDCOLUMNS (
CALENDAR (MinDate, MaxDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)
4 Replies
Hiii MCacc
CalendarTable =VAR StartDate = DATE(YEAR(TODAY()) - 20, 1, 1)VAR EndDate = DATE(YEAR(TODAY()) - 5, 12, 31)RETURNADDCOLUMNS (CALENDAR(StartDate, EndDate),"Year", YEAR([Date]),"Month", MONTH([Date]),"Day", DAY([Date]),"MonthName", FORMAT([Date], "MMMM"),"Quarter", "Q" & FORMAT([Date], "Q"))
this dax would help you to make a calendar dynamically as the year goes on...
Did I answer your question? Mark my post as a solution! And Kudos are appreciatedMCacc , Go to modelling tab and select new table and use
DAX
DateTable =
VAR StartDate = DATE(2004, 1, 1)
VAR EndDate = DATE(2019, 12, 31)
RETURN
ADDCOLUMNS (
CALENDAR (StartDate, EndDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)Thank you,
but is there any way I can achieve this dynamically starting from Today date?
Thank you
CalendarTable =
VAR MinDate = DATE(YEAR(TODAY()) - 20, 1, 1)
VAR MaxDate = DATE(YEAR(TODAY()) - 5, 12, 31)
RETURN
ADDCOLUMNS (
CALENDAR (MinDate, MaxDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)
