Calculate the number of remaining days in a month in Looker Studio

This tutorial will teach you how to calculate the remaining days in a month, the total days, and the days that have already passed. By learning these techniques, you can effectively track and visualize important time-related metrics, improving your data analysis abilities in Looker Studio.

You can follow this step-by-step guide or watch our YouTube video for a more visual walkthrough.

Step-by-step guide with examples

Step 1: Set up your scorecard

Start this process in Looker Studio's edit mode by either making a new scorecard or copying an existing one. This will be the basis for calculating the total number of days in the current month.

Step 2: Calculate the number of days in the month

To calculate the number of days in a selected month, you will need to create a calculated field called Number of days in month and use the following formula.

The formula is designed to work with a date range specified by the created_date field. This field represents a range of dates that may span across two different months. The formula calculates the number of days in the month with the most days within this date range. Here is what each part does:

• Creates a date representing the first day of the month for the earliest date in the created_date range.
• Creates a date representing the first day of the month following the latest date in the created_date range. If the range ends in December, it rolls over to January of the next year.
• The DATE_DIFF function calculates the difference in days between these two dates.
• The MAX function ensures the formula returns the number of days in the month with the most days within the range. This is particularly important when the range spans across two different months.

In summary, this formula calculates the number of days in the month with the most days within the date range specified by created_date.

Step 3: Calculate elapsed days in the month

This step is about finding out how many days have passed in the selected month. You will need to create three calculated fields:

• Last date of range: Captures the last date in your selected range.
• Start of month of last date range: Determines the first day of the month of that last date.
• Elapsed days in month: Calculates the difference in days between these two values.

This approach ensures that the calculation is accurate regardless of whether you are looking at the current month or a historical one. For example, if the last date in your range is November 5, 2023, the elapsed days would be five, even if the start date is in October.

Step 4: Calculate the remaining days in the month

The final step is to find out how many days are left in the month. This is done by subtracting the elapsed days from the total days in the month. So if there are 30 days in November and 5 days have elapsed, you have 25 days remaining.

Conclusion

By following these steps, you can create a scorecard that updates automatically based on the date range you choose. This scorecard is a useful tool for tracking progress throughout the month.

In future tutorials, we will learn how to use this scorecard in a monthly goal tracker, which will help you set and monitor targets based on the number of days that have passed and the number of days remaining in the month.