Finding the average from the last 14 days in Google Sheets

Question

I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the last 14 days average based on the current date.

Does that make sense? Is this possible?

https://docs.google.com/spreadsheets/d/1C8paoYXirR5qus8axs-SciRqxeaBWMxsSmC0PGX969E/edit?usp=sharing

Answer 1

=AVERAGE(QUERY({A2:D}, 
 "select Col4 
  where Col4 is not null 
  order by toDate(Col1) desc 
  limit 14", 0))