Creating composite dates from individual year, month, and day in Google Data Studio

Question

I need to create a YYYYMM format computed column for defining a date in Data Studio since our data is held in separate year, month, and day columns. Unfortunately our the month and day fields are not left zero-padded so a simple concat will not work.

The formula I'm using still uses concat, but also uses todate to parse the hyphenated date string into the compatible format.

TODATE(CONCAT(systems.added_year, CONCAT('-', concat(systems.added_month, concat('-', systems.added_day)))), 'DEFAULT_DASH', '%Y%m')

The problem I'm running into, is that Data Studio doesn't seem to correctly recognize the resultant value, even though it seems to be correct. I'm not sure why, but the YYYYMM field seems to one-month behind even though the result of the calculated field looks correct.

In fact it seems 1-day behind, if I show YYYYMMDD the displayed value is the last day of the previous month.

Here is a screenshot showing the component elements, a string version of the calculated field, and then a Date(YYYYMM) version of the calculated field.

Answer 1

Looks like a bug with the output format. As a workaround, you could output as a full date and then change the column format to YYYYMM.

TODATE(CONCAT(year, CONCAT('-', CONCAT(month, CONCAT('-', day)))), 'DEFAULT_DASH', '%Y-%m-%d')

You could also use '-01' as the last segment.