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 calendar should only go up to 2023 Q1 (March 31, 2023). When a user selects 2023 in the Year slicer, they should only be able to see Q1 in the quarter slicer. Obviously this needs to be dynamic so that when we're in 2022 Q4, the calendar goes up to 2023 Q2 and so on. 

 

What would be the best way to do this? 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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.

     

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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.

     

    • kartiklal7's avatar
      kartiklal7
      Frequent Visitor
      4 years ago

      PaulDBrown ,

       

      This works almost exactly as I'd hoped for. 

       

      The only caveat is if year selected is 2022, you'd have to select "All" or Q1 in the quarter slicer to see 2023 in the year slicer. Selecting Q2, Q3 or Q4, does not show 2023 in the year slicer. 

       

      Wondering if there is a way around this so that 2023 is always visible in the year slicer? 

       

       

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    In that case the simplest solution is to use a hierarchical slicer: