Our company reports on data from the 21st of each month to the 20th of the next month. Is there a way I can set an Advanced date range filter in Data Studio to accomplish this? One that I don't have to manually go and update each month.
Add a comment
|
1 Answer
The answer is yes, there is a workaround through a Google Sheet and a blend in Google Data Studio.
Make a copy of this spreadsheet, and edit the Start Date and End Date formulas according to your needs. They are dynamic DATEVALUE formulas built so that they will change every month. On the Date column there is an ARRAYFORMULA that extracts all the dates in our desired date range.
Then go to Data Studio, connect this spreadsheet as a data source and build a table with the Date as a dimension, and - an important aspect - no date range dimension assigned.
The next step is to create a table with your regular data source, and your desired metrics, and for this one make sure you have a date range dimension assigned.
Blend the two and you will have the data just for your custom range.
The blend has to have the Google Sheet on the left (with the Date as a dimension & no date range) and the regular data source on the right, with all your desired metrics, the Date as a dimension, and the date range set to Auto (as below). The condition through which they’re blended is a left outer join based on the Date dimension.
What results is exactly what you need - a custom rolling date range. Next month it will reset according to the new month's values as defined in the Google Sheets formula.
