[Updated] How to Set ‘Week of Year’ to Start on Sunday in Google Data Studio

[UPDATED 09/22/2020]

With the recent release of Date/Time Functions in Google Data Studio, this tutorial got significantly simpler. How simple? All you need to do now is input the following formula and you will have a ‘Week of Year’ field that starts on Sunday:

DATETIME_TRUNC(Date,WEEK)

In this post, I will take you through my solution for creating a ‘Week of Year’ field that begins on Sunday in Google Data Studio (rather than the default, Monday). Hopefully the Data Studio team creates an out-of-the-box solution in the future, but, until that happens, follow the steps below.

IMPORTANT: Keep all of your field names exactly the same as mine because I reference them when creating other fields.

Step 1: Choose a Dataset with a Date Field

I’m going to assume that if you found this article, you’ve at least made it this far. Congratulations, you’ve completed Step 1. Buckle up for Step 2.

Step 2: Create a ‘Day of the Week’ Field

This one is very straightforward, we need to extract the Day of the Week from our Date field. Funny enough, the WEEKDAY function returns 0 for Sunday, 1 for Monday - which doesn’t require any further manipulation.

WEEKDAY(Date)

Step 3: Create a ‘Day of Month’ Field

Very similar to above, we need to extract the Day of Month from our Date field.

DAY(Date)

Step 4: Create a ‘Day of Month MINUS Day of the Week’ Field

Unfortunately Data Studio is really bad at handling multiple functions at once, and by ‘really bad’, I mean it can’t do it. For this reason, we need to break our functions up into smaller chunks, which is why there are some many steps in this article. This field won’t do much on its own, but we will use it when creating our next field.

Day of Month - Day of the Week

Step 5: Create a ‘Week Starts in Previous Month Flag’ Field

Like I said, we are going to use the field we just created to set a ‘TRUE’ flag if our week starts in the previous month. For example, if your date was ‘9/1/2020’, then the start of the week would be in August ‘8/30/2020’. This function will check for that.

CASE
WHEN Day of Month MINUS Day of the Week <= 0 THEN TRUE
ELSE FALSE
END

Step 6: Create a ‘Month*’ Field

Getting back to the basics, we need to extract the Month from our Date field. I’m using the ‘*’ character because you may already have this field in your dataset.

MONTH(Date)

Step 7: Create a ‘Month when Week Starts in Previous Month’ Field

Now for the fun stuff. We need to tell Data Studio what the previous month is because it doesn’t know. Also, note the CAST function which will store this value as a TEXT field.

CAST(
CASE
WHEN Month*=1 THEN 12
WHEN Month*=2 THEN 1
WHEN Month*=3 THEN 2
WHEN Month*=4 THEN 3
WHEN Month*=5 THEN 4
WHEN Month*=6 THEN 5
WHEN Month*=7 THEN 6
WHEN Month*=8 THEN 7
WHEN Month*=9 THEN 8
WHEN Month*=10 THEN 9
WHEN Month*=11 THEN 10
WHEN Month*=12 THEN 11
END 
AS TEXT)

Step 8: Create a ‘Month when Week Starts in Previous Month*’ Field

If that wasn’t fun enough, we now need to format the field we just created so that it can be accurately interpreted as a date. So instead of ‘1’ for January, we will get ‘01’. Like I said, super fun.

IMPORTANT note the ‘*’ character that makes this field name different from the previous one.

CASE
WHEN Month when Week Starts in Previous Month='1' THEN '01'
WHEN Month when Week Starts in Previous Month='2' THEN '02'
WHEN Month when Week Starts in Previous Month='3' THEN '03'
WHEN Month when Week Starts in Previous Month='4' THEN '04'
WHEN Month when Week Starts in Previous Month='5' THEN '05'
WHEN Month when Week Starts in Previous Month='6' THEN '06'
WHEN Month when Week Starts in Previous Month='7' THEN '07'
WHEN Month when Week Starts in Previous Month='8' THEN '08'
WHEN Month when Week Starts in Previous Month='9' THEN '09'
ELSE Month when Week Starts in Previous Month
END

Step 9: Create a ‘Year*’ Field

Back to the boring stuff. We need to extract Year from our Date field. I’m using the ‘*’ character because you may already have this field in your dataset.

YEAR(Date)

Step 10: Create a ‘Previous Year’ Field

We need a field for the previous year. Luckily years are easy to deal with and this function is very simple.

Year* - 1

Step 11: Create a ‘Year when Week Starts in Previous Month’ Field

Back to the fun stuff. We need to account for when our week starts in a previous year. For example, ‘1/1/2021’ has a week start of ‘12/27/2020’.

CAST(
CASE
WHEN Month*=1 THEN Previous Year
ELSE Year* 
END
AS TEXT)

Step 12: Create a ‘Number of Days in Previous Month’ Field

Another exciting function. We will need this one to eventually figure out what day our start of week should be on. Since the number of days varies from month to month, this function will help us out. I currently don’t have a solution for leap years, but I’ll update this post (and more specifically this function) when I figure it out. Luckily 2020 (although a leap year) is not impacted because the first Sunday in March is March 1st.

CASE
WHEN Month*=1 THEN 31
WHEN Month*=2 THEN 31
WHEN Month*=3 THEN 28
WHEN Month*=4 THEN 31
WHEN Month*=5 THEN 30
WHEN Month*=6 THEN 31
WHEN Month*=7 THEN 30
WHEN Month*=8 THEN 31
WHEN Month*=9 THEN 31
WHEN Month*=10 THEN 30
WHEN Month*=11 THEN 31
WHEN Month*=12 THEN 30
END

Step 13: Create a ‘Day when Week Starts in Previous Month’

OK, these are all fun from here on out. We’re going to use the field we just created to figure out what day our start of week should be on when it's in a previous month.

CAST(Number of Days in Previous Month + Day of Month MINUS Day of the Week AS TEXT)

Step 14: Create a ‘Day when Week Starts in Previous Month*’

Similar to our month field, we need to adjust our day field so that it can be interpreted as a date later on. So instead of ‘1’, we will get ‘01’.

IMPORTANT note the ‘*’ character that makes this field name different from the previous one.

CASE
WHEN Day when Week Starts in Previous Month='1' THEN '01'
WHEN Day when Week Starts in Previous Month='2' THEN '02'
WHEN Day when Week Starts in Previous Month='3' THEN '03'
WHEN Day when Week Starts in Previous Month='4' THEN '04'
WHEN Day when Week Starts in Previous Month='5' THEN '05'
WHEN Day when Week Starts in Previous Month='6' THEN '06'
WHEN Day when Week Starts in Previous Month='7' THEN '07'
WHEN Day when Week Starts in Previous Month='8' THEN '08'
WHEN Day when Week Starts in Previous Month='9' THEN '09'
ELSE Day when Week Starts in Previous Month
END

Step 15: Create a ‘Week when Week Starts in Previous Month’ Field

All of our hard work is starting to come together. This field will act as our start of week when the week starts in a previous month. You probably got that much from the title...

CONCAT(Year when Week Starts in Previous Month, CONCAT(Month when Week Starts in Previous Month*, Day when Week Starts in Previous Month*))

Step 16: Create a ‘Week when Week Starts in This Month’ Field

This is our good old vanilla week start field. When I started on this journey, I thought this is all I would need. Boy was I wrong. Either way, we still need this to handle all the week starts that occur in the same month.

CAST((CAST(Date AS NUMBER) - Day of the Week) AS TEXT)

Step 17: Create a ‘Week of Year (Starts on Sunday)’ Field

Finally! Let’s create the final product - a Week of Year field that actually starts on SUNDAY!!!

IMPORTANT: Convert this field to a Date type when you’re done, so it.. you know… acts like a date.

CASE
WHEN Week Starts in Previous Month Flag = TRUE THEN Week when Week Starts in Previous Month
ELSE Week when Week Starts in This Month
END

Keep in mind that this field will act a little differently than the out-of-the-box Week field. It won’t work well in a time series visual, but you can easily remedy this by using a line visual instead. I hope this was helpful, please reach out if you spot any errors in the set up process.

CompassRed is a full-service data agency that specializes in providing data strategy for clients across multiple industries.