Our company uses QuickSight to perform analytics on sales data. One of the things that is common is comparing today or this month’s sales to last year’s sales.
Using the lag() function or something like periodOverPeriodDifference() would normally be very helpful in doing this, except one major problem. If you, for example, add the filter “Year = 2022” to only show the rows for this year’s data, then both of these functions stop working. This is because both of these functions are post-filter, and there is no calculation level option.
As such, both these functions are completely useless to us. Our work-around has been using a left join in our dataset query, using something like “ON sales.date = date_add(sales_last_year.date, INTERVAL 1 YEAR)” which ends up being computationally expensive and slow for particularly large datasets.