How to Calculate the Number of Days in a Date Range Using DATE_DIFF in Looker Studio

To calculate Ad Spend per Day, we would take the total spend in the time period, and divide it by the number of days that are currently selected in the date control on the report.

How can we capture the number of days that this report covers so that it’s always accurate, even when the date range is changed to the last 30 days or the last year?

We’ll use a calculated field to return the number of days in our currently selected date range. The calculated field is:
DATE_DIFF(MAX(Day),MIN(Day))

Here are the Looker Studio functions involved in that formula:

• The DATE_DIFF function calculates the number of days between two dates, and the first date should be the end date, and then the start date.
• The MAX function returns the highest value, as in the latest date that exists in the currently selected date range.
• The MIN function returns the lowest value, as in the earliest date that exists in the currently selected date range.

📆 Note that the Google Ads connector for Looker Studio uses “Day” for their date field, not “Date” like other Google data sources, including GA4.

Then, our calculated field for the Ad Spend per Day is:
Cost/DATE_DIFF(MAX(Day),MIN(Day))

1. Click Add a chart and select Scorecard
2. In the Scorecard click Metric name and Add Field
3. In Formula add DATE_DIFF(MAX(Day),MIN(Day))
4. Name the field Number of Days
5. Click Apply

Now we are going to do the actual math to calculate the Ad Spend per Day

1. Click Add a chart and select Scorecard
2. In the Scorecard click Metric name and Add Field
3. In Formula add Cost/DATE_DIFF(MAX(Day),MIN(Day))
4. Name the field Average Cost Per Day
5. Click Apply

If you want the Average Cost Per Day to show up as a currency as opposed to a number

1. Click the Data Type dropdown
2. Select Currency and choose the applicable currency
3. Click Apply

Have a question or are having problems getting this calculated field to work for you in Looker Studio? Head over to our YouTube video and leave a comment.