Forum Discussion
kartiklal7
4 years agoFrequent Visitor
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...
- 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.
PaulDBrown
4 years agoCommunity Champion
In that case the simplest solution is to use a hierarchical slicer: