cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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

1 ACCEPTED SOLUTION
Super User

CalendarTable =
VAR MinDate = DATE(YEAR(TODAY()) - 20, 1, 1)
VAR MaxDate = DATE(YEAR(TODAY()) - 5, 12, 31)
RETURN
CALENDAR (MinDate, MaxDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

4 REPLIES 4
Resolver I

Hiii @MCacc

CalendarTable =
VAR StartDate = DATE(YEAR(TODAY()) - 20, 1, 1)
VAR EndDate = DATE(YEAR(TODAY()) - 5, 12, 31)
RETURN
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 appreciated

Super User

@MCacc , 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
CALENDAR (StartDate, EndDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Helper IV

Thank you,

but is there any way I can achieve this dynamically starting from Today date?

Thank you

Super User

CalendarTable =
VAR MinDate = DATE(YEAR(TODAY()) - 20, 1, 1)
VAR MaxDate = DATE(YEAR(TODAY()) - 5, 12, 31)
RETURN
CALENDAR (MinDate, MaxDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q")
)

 Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.