如何在Data Studio中实现对比日数据至当前已结束小时的筛选？

Absolutely! You can totally replicate this "filter to last completed hour" logic in Google Data Studio (now rebranded as Looker Studio) using built-in functions. Let’s walk through exactly how to mirror your Tableau setup:

Step 1: Calculate Your Adjusted Current Hour #

In Tableau, you used DATEPART('hour', Now())-4 to adjust for your time zone offset. In Looker Studio, we’ll use CURRENT_DATETIME() (which pulls the current time based on your report’s time zone) combined with HOUR() and DATETIME_SUB() to get that adjusted hour.

If you need the same -4 hour offset, use this formula:

HOUR(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 4 HOUR))

Pro tip: If your report’s time zone is already set to match your needs, you can skip the offset and just use HOUR(CURRENT_DATETIME()).

Step 2: Build the Filtered Revenue Calculated Field #

Now, create a calculated field that only includes revenue from hours that are less than or equal to your adjusted current hour—just like your Tableau IF [Session Hour (int)] <= [Current Hour] THEN [Revenue GA] END logic.

Assuming your session hour field is named Session Hour (int) and your revenue field is Revenue GA, here’s the formula:

CASE
  WHEN `Session Hour (int)` <= HOUR(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 4 HOUR)) THEN `Revenue GA`
  ELSE NULL
END

This works because Looker Studio ignores NULL values in most aggregations (like SUM), so any revenue from the ongoing hour will be excluded automatically.

Step 3: Apply This to Your Date Comparisons #

To use this for comparing against other dates (like yesterday or last week), you have two easy options:

• Use the calculated field directly in charts: Sum the filtered revenue for your comparison date range—this will only include hours up to the matching adjusted current hour from that date.
• Create a custom filter: Use the same Session Hour (int) <= [adjusted current hour] logic as a filter on your comparison date data to exclude incomplete hours.

Quick Checks to Ensure It Works #

• Verify your report’s time zone (under File > Report settings) to make sure CURRENT_DATETIME() is pulling the right time.
• Test with different times: If it’s currently 2 PM (adjusted), confirm that 2 PM data is excluded and all hours before that are included.
• Check data freshness: Make sure your data source refreshes often enough so the current hour calculation stays up-to-date.

内容的提问来源于stack exchange，提问作者Geoff