MoM and QoQ Metrics Going Blank When Filtering by Single Snapshot Date in QuickSight

Issue: I have a dashboard in Amazon QuickSight that tracks metrics on a weekly basis, with snapshots taken every Monday. I need to calculate and display both Month-over-Month (MoM) and Quarter-over-Quarter (QoQ) percentage changes.

The current issue is that when I filter the snapshot date down to a single week (Monday), the MoM and QoQ metrics go blank. I understand that this happens because the Period-over-Period (PoP) function requires multiple time periods to compare. However, I need a solution that allows me to display MoM and QoQ changes even when filtering down to a single snapshot date.

Requirements:

  1. The MoM calculation should compare the current week’s value to the value from 4 weeks ago.
  2. The QoQ calculation should compare the current week’s value to the value from 12 weeks ago.
  3. The dashboard should retain the functionality of filtering to a single snapshot date while still displaying MoM and QoQ metrics.

Current Setup:

  • Snapshot dates are every Monday.
  • Calculations for MoM and QoQ are set up using the periodOverPeriodDifference function.
  • I have tried filtering the visual by a single snapshot date, but this causes the MoM and QoQ calculations to return blank values.

What I Need:

  • A method to ensure that MoM and QoQ calculations are displayed even when a single snapshot date is selected.
  • If necessary, guidance on whether this can be achieved through QuickSight’s built-in functionality or if a custom SQL query is needed to handle the comparison across dates.

Any suggestions or workarounds would be appreciated.

@gatluakt ,

Have a look at Date hider logic [ https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Year-over-Year-Table-with-Filtering-aka-Date-Hider-]

I believe this is almost similar to the ask here : How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual? - #3 by e2s

Kind regards,
Koushik

1 Like