计算时间范围外值：判断项目记录是否超出起止日期

Alright, let's tackle this problem directly. You need a calculated column that flags whether a time entry falls outside its associated project's start/end date range, and your earlier CALCULATE attempt didn't hit the mark—let's fix that.

前提假设 #

First, let's define the table structure we're working with (adjust names if yours differ):

• Projects (维度表): Contains ProjectID, StartDate, EndDate (one row per project)
• TimeEntries (事实表/你的Main2表): Contains ProjectID, WorkDate (your time entry dates), plus other fields like logged hours. The two tables are linked via ProjectID.

正确的DAX计算列表达式 #

Create a new calculated column in your time entry table with this formula:

Outside Date Range = 
VAR ProjectStart = RELATED(Projects[StartDate])
VAR ProjectEnd = RELATED(Projects[EndDate])
VAR EntryDate = TimeEntries[WorkDate]
RETURN
EntryDate < ProjectStart || EntryDate > ProjectEnd

为什么这个能解决问题？ #

• RELATED() pulls the exact start/end date from the linked Projects table for the current time entry's project—this is key for row-level checks, which is exactly what you need here.
• The logic is straightforward: if the entry date is before the project started OR after it ended, return True; otherwise, False (which matches your example where 2018-05-13 falls within the range, so it shows False).

常见坑点&注意事项 #

• Double-check that your Projects and time entry tables have a valid one-to-many relationship using ProjectID—without this, RELATED() won't work.
• Ensure all date fields are formatted as Date/Time type (not text) to avoid comparison errors.
• If you ever need to exclude the start/end dates themselves (e.g., a date equal to StartDate counts as "outside"), adjust the operators to <= or >=, but your example suggests you want inclusive ranges, so the current logic is correct.

为什么之前的CALCULATE可能失败？ #

CALCULATE is great for modifying filter contexts, but for simple row-level comparisons like this, it's overkill and can lead to context confusion. RELATED() is designed exactly for pulling related values from a dimension table to the fact table at the row level, which fits this use case perfectly.

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