Period over period comparison - show future months

Q&A
, ,
1

Hi everyone,

I’m trying to build a year-over-year comparison in QuickSight, but I’m running into a limitation with the periodOverPeriodLastValue function.

What I want to achieve?
I want to display a line chart where:

  • This year’s data runs from January to the current month (e.g., Jan–Aug 2025).
  • Last year’s data shows all months (Jan–Dec 2024), not just Jan–Aug.
  • Both lines are aligned by month so I can easily compare trends.

Example:

image
image1540×910 63.3 KB

The problem:
periodOverPeriodLastValue only returns values where both periods exist. So if the current year’s data stops in August, it cuts off the prior year’s September–December as well. I need a way to keep the prior year’s full data line.

Constraints:

  • I’m using field Departure Date on the X-axis because it’s tied to a period parameter (e.g. week, quarter, month, year) and I don’t want to lose that functionality.
  • Here is my periodOverPeriod func if that helps:
periodOverPeriodLastValue(sum({Weight}), {Departure Date}, YEAR, 1)

Has anyone have any tips or tricks about how I can make it work? Thanks in advance!

2

Hi @JRoz,
PeriodOverPeriod calculations often cause these types of scenarios as there needs to be an equal period this year to run against the prior timeframe period.

What if you tried creating two separate fields that sum the numbers based on the year; something like:
sumIf({Weight}, truncDate('YYYY', {Departure Date}) = 2025)

Or for something dynamic, you could try utilizing the addDateTime function with truncdate to base it on your different periods.

Let me know if you have any additional questions or if one of these options could work for your case

3

Hi @JRoz,

Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

4

Hi! I like your solution! Me, myself decided to do dynamic LAG (with couple of IF’s depending on what Period the user selected). So basically I lagged this year values to previous year which also got rid of the ‘equal period problem’ :slight_smile:

1 Like