Data Studio - TODATE(input, "%Y%m") Is One Month Behind

Question

I would like to plot a time series by month, but my input data is formatted as "%Y-%m-%d". So, I create another time dimension with the following formula:

TODATE(date, "%Y%M")

My issue is that the new dimension is one month behind what it should be. For example, an input date of '2017-08-15' results in '2017-07' (or that information in whatever data studio's internal date structure is). The result is that changes in the time series metric appear to have occurred a month earlier than they actually did. Here are screenshots of a toy example with google sheets data and the resulting plot. Note that the time series plot is set to cumulative:

Data with an increase in count for August:

Time series plot showing an increase in count in July:

The increase in count in August of 2017 appears to have occurred in July. Although these data are from sheets, the data from my actual issue are from a Postgres DB table, so I don't imagine it's an issue with the data source. What am I doing wrong?

Answer 1

Do note %M stands for Minute, not Month. %m would be considered a 'month' value.

Answer 2

I had the same problem. I fixed it using the PARSE_DATE function.

I have two columns, one with the year and other with the month. First, I CONCAT both of them like this:

This gives me something like "2022/01"

After this, I only need to use the PARSE_DATE function like this.

Which gives me the end result. Being the value: a date with 2022/01. Not like 2021/12.

Answer 3

What worked for me was:

todate(concat(start_date, '01'), '%Y%m%d', '%Y%m%d') 

then choose the date > YYYYmm format.

Answer 4

This is very simple fix, not sure how I overlooked it. Instead of using the function TODATE(), simply change the date format in the data type field to YYYYMM. I must have assumed that this field corresponded to the input format, not output:

Answer 5

Nothing from the listed solutions worked for me. I noticed, however, that

TODATE(start_date,'%Y%m%d','%m')

gives the right month.

I therefore went for a pinch of overkill:

1. CONCAT(TODATE(start_date,'%Y%m%d','%Y'),TODATE(start_date,'%Y%m%d','%m'))
2. Convert to YYYYMM, or '%Y%m' in the source field list.

Answer 6

Similar problem:

1. todate(date, 'DEFAULT_DASH', "%m %Y") resulted in a "month year" configuration that was one month behind.

2. Furthermore, the use of the proposed date format in the data type field to YYYYMM did not work as well.

Solution (for me): I solved by first using the todate function with inclusion of the day (i.e. todate(date, 'DEFAULT_DASH', "%d %m %Y") and then setting the date format to Year Month (YYYYMM).