Datastudio: Blended Data: Datetime can't be aggregated

Question

I have two tables case and party. Both of them are from BigQuery. In the case table, there is a date field in the DATETIME type in BigQuery. I want to plot a chart to show the number of cases per month. It works perfectly when I only use the case table.

However, when I blend the case table with the party table, the aggregation doesn't apply on the date field anymore.

This is how I join the tables.

How do make the aggregation of date & time fields in blends work as normal?

Update

I have tried to create a custom field with TODATE(date, 'RFC_3339', '%Y-%m-%d'), but the aggregation is still not applied to the custom field.

minimal example

1. Create a.csv in local.

a_id,date
1,2022-05-25T00:00:00
2,2022-05-25T00:00:00
3,2022-06-01T00:00:00
4,2022-06-02T00:00:00

2. Create b.csv in local.

a_id,b_id
1,1
1,2
2,3
3,4
4,5
4,6

3. Upload a.csv and b.csv into a Data Studio report.
4. Blend a.csv and b.csv with inner joining on a_id field. I removed "Record Count" from metrics in both tables and added all fields as dimensions.
5. Add a "Table" type chart using the blend created in the previous step.
6. Put date field as the only one dimension and select "Year Month" as the type.
7. Put a_id field as the only one metrics and select "Count Distinct" as aggregation.

actual result

date (Year Month)	a_id
May 2022	2
Jun 2022	1
Jun 2022	1

expected result

date (Year Month)	a_id
May 2022	2
Jun 2022	2

example report

Here

Answer 1

This is a bug, and it was already reported in Google's DataStudio IssueTracker.

• https://issuetracker.google.com/issues/179280180

You probably want to star the issue (Google prioritize issues by the number of affected users).

Also, in the same thread, the reporting user suggests to use the above formula as a workaround.

YearMonthAsDate = DATE(YEAR(date_tran),MONTH(date_tran),1)

It worth a try.