CODEWITHSUNDEEP
looker-studiodatefilter
Alexandra H
Alexandra H

Reputation: 13

Google Data studio -Default filter on last month when data is at month level

I have a report with one data source in Google Data Studio. The data is at month level inside the BigQuery table YYYYMM (for example 202001). I have a filter that is set on the year-month dimension which works ok but I would like to add a default value which changes to last month(max(year_month)) available in the table. Is this possible? I only found the option to hard-code the default selection but this can't be updated dynamically

as in this screen shot

Jaishree's suggestion

Upvotes: 1

Views: 9411

Answers (3)

Edu Marín
Edu Marín

Reputation: 89

You can do it by creating a new field where rows that belong to the current year-month are labeled as 'Current Month' while the others could use your preferred formatting (I'm using "%Y%m - %B %Y" as it allows me to sort report dates in a drop down list).

To do so, you need your date column and CURRENT_DATE() in the same format with the help of FORMAT_DATETIME(). Then, you can use CASE WHEN logic as follows:

CASE 
WHEN FORMAT_DATETIME("%Y%m", MyDateColumn) != FORMAT_DATETIME("%Y%m", CURRENT_DATE()) 
THEN FORMAT_DATETIME("%Y%m - %B %Y", MyDateColumn)
ELSE 'Current Month'
END

Finally, you could use a drop down list control with the new field and use 'Current Month' as the default selection. The main advantage is that the code above will be evaluated every time data is refreshed so no extra automations needed.

Hope it works for you.

Upvotes: 0

Jaishree Rout
Jaishree Rout

Reputation: 392

You can create an another field/column as same year-month and always keep the latest year-month value as "latest month" or any name you want. You can use logic like 

when existing_column = max(year_month) then "latest month" else existing_column

Something like this

  existing_column   new_column
  022020            latest month
  012020            012020
  122019            122019
  112019            122019

You can pass this "latest month" value in the default selection option this option which is just below the dimension. But each time you upload new data you have to update this field like change the table suppose for next month you table should be like

  existing_column   new_column
  032020            latest month
  022020            022020
  012020            012020
  122019            122019
  112019            122019

You can make auto update by scheduling queries as well in big query from where you are loading data to Google data studio.

Upvotes: 1

Nimantha
Nimantha

Reputation: 6471

It can be achieved by setting the Date Range (at the chart, report level, Date Range Control, etc) to Last Month.

First, a YYYYMMDD Date field needs to be created at the Data Source. This can be done using the CONCAT function to add a Day (the 15th is used in the formula) and then using the TODATE function (although in this case the TODATE function is not required, however, adding it as best practice):

TODATE(CONCAT(Date, "15"), "%Y%m%d", "%Y%m%d" )

Adding a link to post on the Google Forums (Nimantha; 10 Feb 2020) which also has a GIF to demonstrate the process above as well as adding the Date field at the Report Level.

Upvotes: 0

Related Questions