I need to create a new report and use incremental refresh in the future. For that I can not use custom SQL while pulling data from data warehouse because then incremental refresh will not work. Problem is I need data from tables that have data for 10+ years, I only need last 6 months worth of data. Can I filter the data in these tables without custom SQL before pulling the data? I can not overload this data warehouse pulling 10+ years of data and after that using filter. My only idea once again is creating a tabular model to another server but that's extra step for me for creating just 1 report.
If I create a view with a WHERE condition going back to almost a year doesn't that mean it still has to go through that same query that I am trying to avoid? This query takes too much time and resources on the data warehouse.
With "Custom SQL" I meant when geting data. Get data-> Sql server. Under advanced options "SQL statement (optional, requires database)" I've learned that if I have anything written there then incremental refresh wouldn't work, so far this is the way I have been geting the data I need without downloading everything. Now I need another way since I have to get incremental refresh working.