CODEWITHSUNDEEP
looker-studio
Marco Herrarte
Marco Herrarte

Reputation: 1620

Google DataStudio - Date Ranges

I'm pretty new to dataStudio and I'm trying to build a simple dashboard with the billing data exported to bigQuery. The thing is that I added a bar chart to show current month expenses but it doesn't match my current bill, after a further investigation I realized the chart is including expenses from the last month as you can see in here:

invoices

The filter clearly shows the current month expenses but it still includes expenses from last month and not sure why. Also, how can I make a filter that considers invoice.month just in case there are some expenses that started on the last day of the previous month and were charged until the next day.

UPDATE # 1:

If I add usage_start_date and usage_end_date you will see that the filter is not working as expected because it considers days from the past month

this_month

UPDATE # 2:

It seems like selecting the field for date range dimension is not available to me for my data source (bigQuery dataset created by GCP from billing data), the same thing on both options: report settings and current page settings

enter image description here

Upvotes: 1

Views: 6137

Answers (2)

Vesa Palonen
Vesa Palonen

Reputation: 282

This is probably because the data source in question has enforced the date range to the partitioned column.

So, you can check this by editing the data source -> Edit connection, and check if the

Use xxx as date range dimension

setting is on.

Upvotes: 1

Nimantha
Nimantha

Reputation: 6482

Summary

  1. Create the Calculated Field below;
  2. Ensure that the Date Range Dimension in the charts as well as Report (or Current Page) is set to the newly created Date Field.

1) Dates: TODATE & CONCAT

The invoice.month field is currently not a Google Data Studio recognised Date; creating the Data Source-level Calculated Field below will do the trick:

One approach is to use a complete YYYYMMDD Date, which can be achieved by using the CONCAT function to add a Day component (the 15th is used below), and then incorporating the TODATE function to ensure that the Date format is recognised:

TODATE(CONCAT(invoice.month, "15"), "%Y%m%d", "%Y%m%d")

Google Data Studio Report and a GIF to elaborate:

2) Dates: Date Range Dimension

Ensure that the Date Range Dimension in the chart as well as the the Report (or Current Page) is set to a Google Data Studio recognised Date Field; this can be achieved by:

2.1) Report Settings

  • There are two ways to access Report Settings:
    • File > Report Settings;
    • Right click on a blank space on the page and select Report Settings from the Drop-down;

2.2) Data Source

  • Select the respective Data Source;

2.3) Date Range Dimension

  • Select the newly created Date Field from the list of fields.

Added a New Page to the Report as well as a GIF to demonstrate:

Upvotes: 5

Related Questions