Date Table Exceeding Max

[Anonymous]

Hi All,

I am trying to restrict the YY-MM that appear in my visuals to the maxium being Today() but it showing upto 2023 for some reason.

Date = 
VAR MinYear = YEAR ( MIN ( 'Flights'[Start date ] ) )
VAR MaxYear = YEAR ( MAX ( 'Flights'[Start date ] ) )
VAR MaxToday = TODAY()

RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO(7),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxToday )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Year Month",FORMAT([DATE],"YY-MM"),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Week End Date",  [Date]+ 7-1*WEEKDAY([Date],2),
    "Week Number", WEEKNUM([Date],2),
    "Today", MaxToday,
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

Date TableYear Month

You can see that Today() is correct but the year goes to next year and IDK why.

Thanks for any help.

[amitchandak · Accepted solution]

@Anonymous , try like 

Date = 
VAR MinYear = date(YEAR ( MIN ( 'Flights'[Start date ] ) ),1,1)
VAR MaxYear = date(YEAR ( MAX ( 'Flights'[Start date ] ) ),12,31)
VAR MaxToday = TODAY()

RETURN
ADDCOLUMNS (
    calendar(MinYear,MaxYear),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Year Month",FORMAT([DATE],"YY-MM"),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Week End Date",  [Date]+ 7-1*WEEKDAY([Date],2),
    "Week Number", WEEKNUM([Date],2),
    "Today", MaxToday,
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)