DateDiff with seconds

[Anonymous]

Hi Everyone,

I'm trying to calculate the difference between the two dates using the DATEDIFF() function and I want to omit weekends from it. below is the DAX I'm using currently:

Diff =

VAR _d = DATEDIFF([StartDate], [EndDate], SECOND)

VAR _min = DIVIDE(_d,60)

VAR _hour = DIVIDE(_min,60)

VAR _day = DIVIDE(_hour,24)

VAR _weekend = CALCULATE(COUNT(Dates[Date]),ALL(Dates),FILTER(Dates, Dates[DayName] in {"Saturday", "Sunday"} && Dates[Date]>=SELECTEDVALUE('2C_Table'[StartDate]) && Dates[Date]<=SELECTEDVALUE('2C_Table'[EndDate])))

Var _check = _day - _weekend

return

IF(_check< 0, 0, _check)

The above DAX is returning the value, however, it's not omitting Weekends from the calculation.

Any help on this would be appreciated!!

Regards,
Mahesh

[SamInogic · Accepted solution]

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!

[Anonymous]

Hi @SamInogic ,

Thank you for sharing the DAX and its explanation. However, it's giving a blank value when I used the given DAX.

Regards,
Mahesh

[rajendraongole1]

Hi @Anonymous  - Your approach is on the right track, but the issue comes from how you’re filtering weekends in your _weekend variable. The ALL(Dates) function removes the applied filter context, but you need to ensure that the count correctly aligns with the StartDate and EndDate range.

check the modified measure:

Diff =
VAR _TotalDays = DATEDIFF(SELECTEDVALUE('2C_Table'[StartDate]), SELECTEDVALUE('2C_Table'[EndDate]), DAY)

-- Count the weekends between StartDate and EndDate
VAR _WeekendCount =
CALCULATE(
COUNTROWS(Dates),
Dates[Date] >= SELECTEDVALUE('2C_Table'[StartDate]) &&
Dates[Date] <= SELECTEDVALUE('2C_Table'[EndDate]) &&
Dates[DayName] IN {"Saturday", "Sunday"}
)

-- Subtract weekend count from total days
VAR _WorkDays = _TotalDays - _WeekendCount

-- Ensure no negative values
RETURN MAX(0, _WorkDays)

Hope it helps

[Anonymous]

Hi @rajendraongole1 ,

Thank you for sharing the DAX and its explanation. However, it's giving a blank value when I used the given DAX.

Regards,
Mahesh