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!

1 Like
2

Hello @ivy_coke

Can you clarify what the ‘Include’ and ‘Exclude’ part of your calculations are doing? Are those values then referred to in another calculation?

3

Include is some specific month I want to include in the visual. For example, Dec 2023, Dec 2024, Jan 2025, Feb 2025.
Exclude is other dates in the dataset.
Also after adding the dropdown list, can I specify the default date to the recent month end date. For example, Feb 2025.
Thanks!

4

Hello @ivy_coke

For the include and exclude portions of your calculation, do they then connect to another filter or are you wanting the control to manage that? Taking a step back to clarify, you want a dropdown filter for users to choose either previous years (represented by the last date in that year) or current year up to most recent complete month.

You’ll need to to explicitly say what dates you want included in your parameter control.