How to Calculate the Number of Days Between Start and End Dates 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 Google Ads 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”

In the example shared in the video, the calculated number of days is 28 days, but the date range we are looking at was 30 days, this is because Looker Studio is only going to count the number of days that you are actually running ads using Google Ads data source. Based on your own data sources, when you do this calculation just double check the information.

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.