View the original community article here
Last tested; Jan 3, 2020
For some analysis, you might want to get only the completed weeks in the last quarter, for example, if the first day of Q4 2019 (2019-10-01) is Tuesday, then the start day should be 2019-09-30, and the last day of the quarter is 2019-12-29 (Sunday).
We can use the below SQL to create a yesno filter, the yes value will filter out all the dates of the complete weeks.
BigQuery:
${date_dimension}>=date_trunc(date_add(date_trunc(current_date(), quarter), interval -1 quarter), ISOWEEK)
AND
${date_dimension}<date_add(date_trunc(date_add(date_trunc(current_date(), quarter), interval -1 week), ISOWEEK), interval 1 week)
Redshift and Snowflake:
${date_dimension}>=date_trunc('week', dateadd('quarter', -1, date_trunc('quarter', current_date)))::date
AND
${date_dimension}<dateadd('week', 1, date_trunc('week', dateadd('week', -1, date_trunc('quarter', current_date))))::date
Postgres:
${date_dimension}>=date_trunc('week', date_trunc('quarter', now())-interval '3 months')::date
AND
${date_dimension}<(date_trunc('week', date_trunc('quarter', now())-interval '1 week')+interval '1 week')::date
This content is subject to limited support.