dataframe - How to create and auto-calculate a unified 'Date Range Dimension' across multiple tables in Looker S

I am working on a scorecard in Looker Studio (formerly Google Data Studio) using blended data from multiple tables. Here are the details:

1. Blending Setup:

   • I am using full joins based on the condition a.date = b.date to combine data from multiple tables.
   • Each table has its own date field (e.g., Table A.Date1, Table B.Date2, etc.).

2. Transformations:

   • I have applied CASE conditions and transformations to calculate a final number for the scorecard.
   • These calculations are working correctly for now.

Requirement:

I want to introduce a "Date Range Dimension" that:

1. Filters the blended dataset based on a unified date range.
2. Automatically combines the date ranges from all tables in the blend.
3. Ensures calculations work even if dates are missing from some tables.

Example Scenario:

• Date Range: November 1 to November 5.
• Tables:
  • Table 1: Contains data for 3 dates (e.g., Nov 1, 2, and 3).
  • Table 2: Contains data for 1 date (e.g., Nov 3).
  • Other tables have no matching dates in the range.

Expected Result:

• The scorecard should calculate correctly for the date range (Nov 1 to Nov 5).
• Missing data from some tables should not break the calculations.

Questions:

1. How can I create a unified Date Range Dimension in Looker Studio when blending multiple tables with different date fields?
2. Is it possible to auto-calculate metrics for a date range (e.g., Nov 1–5) even if data for some dates is missing in some tables?
3. Are there any best practices or workarounds to achieve this in Looker Studio?

Any suggestions or guidance on implementing this in Looker Studio would be greatly appreciated!

Additional Notes:

• I am blending 5 tables in total.
• Using full joins for all date-related blending.