Excluding Weekends in Power Bi when calculating the difference in dates and times

Question

I've been doing a lot of research on this question over the past few days, but none of the articles I've researched have been able to do. I'm trying to find the difference between two dates in Power BI that excludes weekends, but it also needs to calculate the differences in times as well. All of the articles I've seen have been able to find the difference in the dates themselves, but for my specific problem, the times are important.

For example,

I'm trying to find the difference between Pickup Date/Time and Created Date/Time. The data I am looking at is to find all the shipments that were entered into the system two business days in advance of the pickup date, and those shipments need to be entered in before noon that day.

I've created a Date table that has all dates between 2018-2020 that show whether it's a weekend or a weekday, but I haven't been able to figure out how to basically do Pickup From - Created From and filter out the weekends.

Any help with this would be greatly appreciated.

My data is below that i'm trying to use.

Supplier Lead Times Table with Created/Date Time and Pickup From/Date Time

Lead Time Days

The Lead Time days currently is just ('Supplier Lead Times' [Pickup From Date/Time] - 'Supplier Lead Times'[Created Date/Time]).

Even if I could use the DATEDIFF HOURS function for this, that would work, i'm just having trouble filtering out the weekend days. I have a calendar table that has all the weekdays/weekends

Calendar Week Table

Try adding some code to the question to see how you're doing it. — Juan Ferrer
Does this question, Exclude weekends in PowerBI report?, have the answers you need? — Rick Smith

Eray Balkanli Eray Balkanli · Accepted Answer · 2018-12-18T18:44:36

As far as I checked, your "Create Date" and "Pickup Date" fields are always weekdays. Therefore, if we calculate the total difference than put 24 hour back for each weekend, that would work. Try to create a quick measure column like below and apply it to your table:

Difference = 

var weekends = 
SUMX(
    SELECTCOLUMNS(
        CALENDAR(min('MyTable'[CreatedDate]), min('MyTable'[PickUpDate])),
        "Date", min('MyTable'[CreatedDate]),
        "BDay", IF(WEEKDAY([Date],3) >= 5, 1, 0)
    ),
    [BDay]
)

return DATEDIFF(min('MyTable'[CreatedDate]), min('MyTable'[PickUpDate]), HOUR) - weekends*24

Excluding Weekends in Power Bi when calculating the difference in dates and times

1 Answers