DateDiff with seconds

Hi,

As per our understanding you are attempting to calculating the difference between the two dates using the DATEDIFF() function.To omit weekends from the date difference calculation in Power BI using DAX, you need to adjust your logic to correctly exclude Saturdays and Sundays. The issue with your current DAX is that it calculates the total difference in days and then subtracts the count of weekend days, but this approach doesn't account for the fact that weekends are already included in the total day count.

Here’s a revised version of your DAX formula that correctly excludes weekends:

Diff =

VAR StartDate = SELECTEDVALUE('2C_Table'[StartDate])

VAR EndDate = SELECTEDVALUE('2C_Table'[EndDate])

VAR TotalDays = DATEDIFF(StartDate, EndDate, DAY)

VAR WeekendDays =

    CALCULATE(

        COUNTROWS(Dates),

        FILTER(

            Dates,

            Dates[Date] >= StartDate &&

            Dates[Date] <= EndDate &&

            (WEEKDAY(Dates[Date], 2) >= 6)  // 6 = Saturday, 7 = Sunday

        )

    )

VAR BusinessDays = TotalDays - WeekendDays

RETURN

    IF(BusinessDays < 0, 0, BusinessDays)


Explanation:

StartDate and EndDate: These variables store the start and end dates from your table.

TotalDays: This calculates the total number of days between the start and end dates using DATEDIFF.

WeekendDays: This calculates the number of weekend days (Saturdays and Sundays) between the start and end dates. The WEEKDAY function is used to identify weekends (6 = Saturday, 7 = Sunday).

BusinessDays: This subtracts the number of weekend days from the total days to get the number of business days.

RETURN: The final result is returned, ensuring that if the calculation results in a negative value, it returns 0.

Thanks!