Forum Discussion

MCacc's avatar
MCacc
Helper IV
2 years ago
Solved

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 ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    MCacc 

     

    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")
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

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

  • MCacc's avatar
    MCacc
    Helper IV
    2 years ago

    Thank you, 

     

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

     

    Thank you

    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      2 years ago

      MCacc 

       

      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")
      )