Power BI排除周末的日期差计算结果异常，请求协助

Let's troubleshoot why your formula is returning 4 days instead of the expected 1-day difference. This is a common DAX context issue, so let's break it down and fix it step by step:

1. First, Verify Your IsWorkDay Column #

The foundation of your calculation relies on correctly marking weekdays. Double-check that your Dates[IsWorkDay] column uses the right logic:

IsWorkDay = IF(WEEKDAY(Dates[Date], 2) <= 5, 1, 0)

• WEEKDAY([Date], 2) returns 1 for Monday, 5 for Friday, and 6-7 for weekends. This formula marks weekdays as 1 and weekends as 0. If this was reversed (e.g., marking weekends as 1), your sum would be drastically off.

2. Why Your Original Formula Fails #

Your current formula:

CALCULATE(SUM(Dates[IsWorkDay]), DATESBETWEEN(Dates[Date], 'table1'[date1], 'table1'[Date2]))

The problem here is context propagation. If Dates and table1 have an existing relationship, CALCULATE inherits the existing filter context from table1, which can skew the date range you're trying to target. Even without a relationship, DATESBETWEEN might not properly respect the row-level date1/date2 values from table1, leading to an unintended larger date range being summed.

3. Correct Formula Options #

Use either of these reliable alternatives that explicitly handle row-level context:

Option 1: SUMX (Iterative Sum) #

This iterates over the filtered date range and sums only the valid workdays:

WorkDays = SUMX(
    FILTER(
        Dates,
        Dates[Date] >= 'table1'[date1] && Dates[Date] <= 'table1'[Date2]
    ),
    Dates[IsWorkDay]
)

Option 2: COUNTROWS (More Intuitive) #

If you just need a count of workdays, this is even clearer—it counts rows where the date is in range and marked as a workday:

WorkDays = COUNTROWS(
    FILTER(
        Dates,
        Dates[Date] >= 'table1'[date1] && Dates[Date] <= 'table1'[Date2]
        && Dates[IsWorkDay] = 1
    )
)

4. Quick Test to Validate #

Pick a row in table1 where date1 and date2 are 1 day apart (e.g., date1 = 2024-05-20 (Monday), date2 = 2024-05-21 (Tuesday)). Both formulas should return 2 workdays (or 1 if they're the same date). If they do, your original context issue was the culprit.

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