How to Create a Dropdown Filter with Specific Month-End Dates in QuickSight

Question & Answer
, , ,
1

I have monthly snapshot data with a column called month_end_date formatted as YYYY-MM-DD (e.g., ‘2025-03-31’). I want to create a dropdown filter that displays only specific month-end snapshots:

  • Explicitly show the previous three year-end months: December 2022 (2022-12-31 ), December 2023 (2023-12-31 ), and December 2024 (2024-12-31 ).
  • Dynamically show all months from January 2025 onwards but exclude the current ongoing month (e.g., if today is March 2025, include only January 2025 and February 2025, but not March).

I have attempted to use a calculated field similar to this:

ifelse(
{month_end_date} >= parseDate(‘2025-01-01’, ‘yyyy-MM-dd’) AND {month_end_date} < parseDate(‘2025-03-01’, ‘yyyy-MM-dd’),
‘Include’,
ifelse(
{month_end_date} = parseDate(‘2022-12-31’, ‘yyyy-MM-dd’)
OR {month_end_date} = parseDate(‘2023-12-31’, ‘yyyy-MM-dd’)
OR {month_end_date} = parseDate(‘2024-12-31’, ‘yyyy-MM-dd’),
‘Include’,
‘Exclude’
)
)

However, when applying this filter and then creating a dropdown filter, QuickSight still shows all months or does not behave as expected.

Could someone guide me on how to correctly set up a calculated field and dropdown filter in QuickSight to achieve my desired functionality clearly?

Thanks in advance!