Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date Table Exceeding Max

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.

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

1 Reply

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