I am new to Looker, however not new to BI tooling in general. I have an OBT (one big table) loaded into Looker with ~30k financial fact records per day, on which I have an aggregated report. All records individually have an as_of_date available (on which I pivot the table).
The idea is that in the report you can select a base date (which by default is the last business day, or max date available in the OBT if that is easier to implement). And a comparison date, which by default should be the day before the last business day. The comparison date should always be before the base date.
I use a pivot table with field calculations to calculate the difference in moves between those two selected days, e.g. orders, revenue, profit went X % up or down compared from the base to the comparison date.
I am struggling to implement this in Looker in a correct manner, any suggestions? I am using a LookML dashboard, but already struggling to connect two date filters to one as_of_date (e.g. only one works), or dynamically setting default values for the filters.
Any suggestions, tips, or recommendations to implement this in a way which make the user experience as straight forward as possible are highly appreciated.