Based on the Date and the Spend Column, I am trying to calculate the 7-Days average of the Meta Spend, I am not entirely sure how this is achieved in looker studio, as i need a formula that sums the Amount spent over the last 7 days and then divides it by 7

SUM(CASE WHEN DATE_DIFF(TODAY(), Date) <= 7 AND DATE_DIFF(TODAY(), Date) >= 0 THEN Amount spent ELSE 0 END) / 7

Divides each row by 7, but does not calculate the rolling average for the column giving me