I'm trying to write a beast mode to give me the last value depending on how the time data are grouped (e.g. by week, then value on last day of each week; by month, then value on last day of each month). I believe I could do this in magic ETL but then the end user can no longer choose the time grouping they're interested in dynamically on the card.
The columns are: [member_count, date] with example values like: [100, 2024-01-15]
In the current format of the data there might not be values for every single day in a timeframe (e.g., 2024-01-16 might be missing). I could probably fill in every date if it would help.
I tried doing something like: CASE WHEN date = MAX(date) THEN member_count ELSE 0 END
But then I wasn't able to aggregate that beast mode - I think MAX(date) may not be the right choice. Thanks for any help!