Rolling 12 month Calculation

Question & Answer
,
1

I’m working on a dashboard where I need to calculate a Rolling 12-Month Total. The calculation works perfectly when I display all the months of data. However, when I apply a filter to display only the past 3 months, the rolling sum calculation automatically adjusts to include only those 3 months, instead of the full 12 months.

Example:

  • Data for Months 1 to 12:
    • Month 1: 100
    • Month 2: 100
    • Month 3: 100
    • …
    • Month 12: 100

When I display data for all 12 months, the rolling 12-month total works as expected:

  • Month 1: 100
  • Month 2: 200
  • Month 3: 300
  • …
  • Month 12: 1200

However, when I filter the data to only show the past 3 months (e.g., months 10, 11, and 12), the calculation adjusts to only consider those 3 months, which gives me:

  • Month 10: 100
  • Month 11: 200
  • Month 12: 300

I want the calculation to always consider the last 12 months, even when filtering for just the last 3 months. I’m currently using the windowSum function.

Can anyone help me understand how to achieve this in QuickSight? Any advice or suggestions would be appreciated!

2

Hi @javvajim

However, in the visualization do not filter on the date-range. You should handle the calculation of your data in the calculated fields.

This article should help you

3

I have checked this article and its not helping. Can some one help how can I do my calculations in calculated fields

4

Hi,
try using Level aware calculations. You can find it in DemoCentral. I think it will be usefull for your case.

5

Checkout this solution from DemoCentral

2 Likes
6

I was scratching my head how could this be done! Thanks for sharing this link!

1 Like
7

Hi @javvajim

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

8

Hi @javvajim,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!