Hi Team
I am trying to create a table that shows one as at date, the total balance for that date and the average balance for the last [7 or n] days (example in screenshot).
I can create this when I have 7+ days of data showing in the table (screenshot). This gives the correct average of 868,422. However when I filter the table to only show current date (desired output) the average amount recalculates to show the average equal to total balance (incorrect at 907,349).
The formula I used for average is: avgOver(sum({total_balance}), [(rank([{date_as} DESC]))<=7], POST_AGG_FILTER)
When I try to change to PRE_FILTER and remove the sum I get the error ‘Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands’.
I’m sure I am missing something very obvious. Request is: how can I get the table with 1 date row to show total balance of 907,349 and average of 868,422.
Thanks!