Forum Discussion

kartiklal7's avatar
kartiklal7
Frequent Visitor
4 years ago
Solved

Create Calendar Table with Max Date as Current Quarter + Next Two Quarters

Hi All,   I want to create a calendar table or edit my current calendar table so that the max date on this is the current quarter + the next 2 quarters.   So if we're in 2022 Q3 the dates in my c...
  • PaulDBrown's avatar
    4 years ago

    Try:

    Calendar Table =
    VAR _MinDate =
        DATE ( 2021, 1, 31 ) //Enter the minimum date or the expression to calculate the minimum date in your dataset
    VAR _QToday =
        QUARTER ( TODAY () )
    VAR _MaxYear =
        SWITCH ( TRUE (), _QToday < 3, YEAR ( TODAY () ), YEAR ( TODAY () ) + 1 )
    VAR _MaxDate =
        SWITCH (
            _QToday,
            1, DATE ( _MaxYear, 9, 30 ),
            2, DATE ( _MaxYear, 12, 31 ),
            3, DATE ( _MaxYear, 3, 31 ),
            4, DATE ( _MaxYear, 6, 30 )
        )
    RETURN
        ADDCOLUMNS (
            CALENDAR ( _MinDate, _MaxDate ),
            "MonthNum", MONTH ( [Date] ),
            "Month", FORMAT ( [Date], "MMM" ),
            "Year", YEAR ( [Date] ),
            "QuarterNum", QUARTER ( [Date] ),
            "Quarter", "Q" & QUARTER ( [Date] )
        )
    

    As for the filtering, can you please clarify what you  are after? With the default slicer behaviour, if you filter for 2023 you will only see Q1, since there are no rows with other quarters in the calendar table for 2023.