I think I'm close on this but am struggling to get my expected result. Here is my end goal—a chart showing data by date, with a line for the "Best Ever" value.
Things making it tricky:
- I need the date range to be dynamic, i.e. user can select different dates
- I need the "best ever" value to be pulled from across the entire dataset, not just the displayed dates.
- The dataset is not structured with 1 row per date. Sometimes multiple rows exist for a single date.
I have the line working using the following windowed beast mode: MAX(SUM(Total Revenue)) OVER ()
however, this pulls the max from the filtered/displayed dates. I need the max from the entire dataset (all time).
If only I could add the DENY FILTER date
from FIXED functions into my window, I would have what I want 🙂 but obviously it doesn't work that way. Using a FIXED function, e.g. MAX(SUM(Total Revenue) FIXED (BY date))
because I have date
on the x axis; and MAX(SUM(Total Revenue) FIXED ())
doesn't work either.
Any ideas that don't involve ETL? ETL isn't an option because I also need to do this for calculations and I need them to be filterable by location — in order to keep everything contextual, it needs to be done in beast mode. TIA!!!