Pivot table - showing data being filtered off

[lbl]

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.

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

[Brett]

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 QuickSight 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

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

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…

[lbl]

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.

Any other alternatives?

Thanks!

[lbl]

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