I have several reports (in Power BI, if it matters) that source from SQL views (in Azure SQL MI, if it matters). I’m having an architecture problem with my usage of SQL views. A lot of them use “relative date filtering”. For example, one of my views returns date data from a date dimension, where the view filters the date dimension table by a max date as determined from a fact table. It would be great if I could use a variable in a view for this, but views don’t support that. So, my workaround is to use CTEs (to determine the relative date) and CROSS APPLY (to act like variable) in the views. This feels architecturally wrong to me. I do a lot of relative date filtering like this (often multiple instances in a view, in a lot of views).

So, is this (using CTEs and CROSS APPLY to emulate variables) actually a best practice (for the context of putting reports over SQL views)? If not, what is best practice? I have considered pushing the date filtering down to the physical table layer (data mart layer), but that will mean a proliferation of physical data marts (or, at least the creation of tables that aren’t really part of the data model), which will be difficult to govern.