Forum Discussion
Create Calendar Table with Max Date as Current Quarter + Next Two Quarters
- 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.
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.
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?