# Pivot table - showing data being filtered off

**URL:** <https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731>\
**Category:** Q&A\
**Tags:** pivot-table, calculations, feature-request, Business-Intelligence-Engineer, quick-sight\
**Created:** [July 1, 2025, 10:48am UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731 "2025-07-01T10:48:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![lbl](https://avatars.discourse-cdn.com/v4/letter/l/9f8e36/32.png) [@lbl](https://community.amazonquick.com/u/lbl)\
**Post date:** [July 1, 2025, 10:48am UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731/1 "2025-07-01T10:48:26Z")

</div>

Dataset is from a csv file with the following data.  
invoicedate,Product,Sales SGD,month  
1/11/2024,A,100,1  
1/11/2024,B,200,1  
2/12/2024,A,300,2  
2/12/2024,C,400,2  
3/13/2024,A,500,3  
3/13/2024,B,100,3  
4/14/2024,A,200,4  
4/14/2024,B,300,4  
5/15/2024,A,400,5

Calculated field **SalesPrevMth** is created as “lag(sum({Sales SGD}), [invoicedate ASC], 1, )”  
Pivot table is created as below and working fine.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/f/8/f8969f0981c550e2c7e871265e65c4bef3a29aa8.png)

However, after Jan 2024 is filtered off. SalesPrevMth and Sales % become blank. How to still show them even when Jan 2024 is not showing?

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/f/e/fe4f3d024c98bbfc05c0800c3f73f0ea8ed1ef49.png)

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [July 1, 2025, 2:33pm UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731/2 "2025-07-01T14:33:20Z")

</div>

Hi @lbl,  
The February values (or rather January’s since it’s previous month), is not populating because the lag calculation is a table function; meaning it can only access the data that’s not filtered out.

Below is a really useful guide to order of operations within Quick Sight as well as a further breakdown of various Period over period computations.

Check these out and let me know if you have any additional questions.

> **[Order of evaluation in Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/order-of-evaluation-quicksight.html)**
>
> Understand the sequence in which operations run in Amazon QuickSight.

> [@Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)](https://community.amazonquick.com/t/period-over-period-and-other-date-comparison-calculations-yoy-mom-ytd-rolling-90-days-etc/564):
>
> Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

---

<div class="post-metadata">

**Author:** ![lbl](https://avatars.discourse-cdn.com/v4/letter/l/9f8e36/32.png) [@lbl](https://community.amazonquick.com/u/lbl)\
**Post date:** [July 2, 2025, 12:32am UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731/3 "2025-07-02T00:32:34Z")

</div>

hi @Brett  
The resource shared does not work for my case.  
As the resource solution is based on only **a single month** , while I have rows of Month.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/e/5/e5ee6f7675f0445b068fbbf37fdb98f6b820f27e.png)

Any other alternatives?

Thanks!

---

<div class="post-metadata">

**Author:** ![lbl](https://avatars.discourse-cdn.com/v4/letter/l/9f8e36/32.png) [@lbl](https://community.amazonquick.com/u/lbl)\
**Post date:** [July 14, 2025, 12:55am UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731/4 "2025-07-14T00:55:49Z")

</div>

hi @Brett  
Any update on this, please.  
Thank you.

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [July 21, 2025, 4:12pm UTC](https://community.amazonquick.com/t/pivot-table-showing-data-being-filtered-off/47731/5 "2025-07-21T16:12:15Z")

</div>

Hi @lbl,  
Apologies for the delayed response. After running some additional tests, I think this is limitation that does not have a current work around. Quick Sight does not allow you to utilize PRE\_AGG or PRE\_FILTER with the lag function, therefore making it hard to incorporate data that you’ve already filtered out of the visual.

The best work around I can suggest for this instance; add an additional field to your dataset that is labeled ‘prior month sales’ and then pulls the sales from dates registered to prior month.

Additionally, I’ve seen this scenario arise in the community before so this is a fairly common ask; so I’ll mark this as a feature request as it would be nice for an out of the box option to be added allowing you to incorporate prior month values in this instance.
