Hack: How to add a moving weeks out bar to your Looker Studio Tracking Dashboard

How To Add It

‍

The formula used

IF(weeksOut = DATETIME_DIFF(DATE(2024,08,15), TODAY(), ISOWEEK), 1, NULL)

Breaking it down:

DATE(2024,08,15)

This sets the date of your event. You can either 'hard code' it, as shown here, or use the stored event date dimension, as we go through in our other courses.

DATETIME_DIFF(DATE(2024,08,15), TODAY(), ISOWEEK)

This calculates the current ISO week difference between today and your event date.

TODAY(): Retrieves today's dateDATETIME_DIFF: Calculates the difference between two datesISOWEEK: Specifies that the difference should be calculated in terms of ISO weeks. ISO weeks start on Monday, which aligns with how many people think of a week.

This is then wrapped in an IF statement.

IF(weeksOut = ... , 1, NULL)

i.e. If the weeksOut metric for your x-axis is equal to the DATETIME_DIFF calculation, give us 1. IF NOT, give us NULL. As mentioned in the video, you can replace NULL with 0, and the formula below will have the same outcome.

IF(weeksOut = DATETIME_DIFF(DATE(2024,08,15), TODAY(), ISOWEEK), 1, 0)

What makes this 'hack' work is plotting the value of this metric to the right-hand axis, as shown in the walkthrough.