DAX BusinessDaysDifference Excluding EndDate

Code Extension in DAX for BusinessDaysDifference Calculation

Objective:

Extend the existing DAX code snippet to exclude the last day in the period from being counted in the BusinessDaysDifference calculation.

Updated Code Approach:

To achieve the desired functionality, it is necessary to adjust the filtering logic within the FILTER function to exclude the EndDate from the calculation.

Updated DAX Code:

BusinessDaysDifference = 
VAR StartDate = [date1]
VAR EndDate = [date2]
RETURN
IF(
    ISBLANK(StartDate) || ISBLANK(EndDate),
    BLANK(),
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate),
            AND(
                WEEKDAY([Date], 2) < 6,
                [Date] < EndDate
            )
        )
    )
)

Code Explanation:

1. The AND function is introduced to add an extra condition to the FILTER.
2. The new condition [Date] < EndDate ensures that the EndDate is excluded from the date range considered for counting business days.
3. By incorporating this adjustment, the BusinessDaysDifference calculation now correctly omits the last day in the period.

By integrating the provided code changes, the BusinessDaysDifference measure will exclude the last day in the period, aligning with the specified requirement.