Something is suspicious about sum(sales_amt) giving you 2010 data without selecting 2010. Something is not right. The expression (ad_yr_num)-1 shouldn't work either unless you have just one year selected. Maybe if you looked deeper into this strange result you might find something that's wrong with the data model.
Also, to make your application more efficient delete all unnecessary columns and consolidate your data model into one table as much as possible.
I've never had problems with a slow max o min especially with a column like years where the number of distinct years is so few.
Thank you for your response. Yes, there was a hidden list box with 2010 selected which make sense to why i was getting the right results for 2010.
It is one big fact table with some 80 million rows. On one sheet i have 2 charts: one barchart with only 2 measures and one straight table with 2 dimensions and 32 measures that's doing all the TY and TYLY sum by different columns. It takes about 12 seconds to get the results back on the table and memory jumps to almost 500 MB for each selection clicks made which seems too much of memory consumption for just one click. All unnecessary columns have been removed from the data model as well.
Appreciate any additional feedback. Thanks.