QuickSight should have the ability to use the ‘calculation level’ in the windowSum/WindowAvg functions. It would allow the developer to create rolling metrics (such as a rolling 12 month average) independent of the dashboard filters.
For example, if the year/month 2021-06 is selected, the rolling 12 average for a metric would still calculate using 2020-07 to 2021-06 but wouldn’t show the prior months’ data values and would hide them (2020-07 to 2021-05 would be hidden).
Currently, if a user filters to one year or one year/month, only those remaining months of data are used in the calculation. This can be accomplished with the parameters:
, [sort_order_field ASC/DESC, …]
,[ partition_field, … ]
, calculation level → ** Currently not a parameter but if added, PRE FILTER would be a useful function in the situation laid out above.**
Is there any alternative??