In DAX, can I create a function that calculates the working days between two date/time fields?

Question

There are already plenty of options for calculating the number of workdays between two dates if there are no times involved, but if you leave it as date/time, is there any way to get a number of working days (with decimal remainders) between two points in time in DAX (e.g. Power Query/Power BI)?

Two columns, Date/Time format, so, like: Start Date 6/5/2018 8:00 AM Finish Date: 8/5/2018 5:00 PM creating a calculated column that calculates the number of working days (excludes weekends) between those dates. — jpford

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-13T15:58:34

Assuming that your start and end times occur on working days, then you should be able to take the time difference between two dates and subtract out the number of non-working days during that period.

You'll want a calendar table to help out. Say,

Dates = CALENDARAUTO()

Then your working days measure might look like this:

WorkingDays =
    StartFinish[Finish Date] - StartFinish[Start Date] -
    SUMX(
        FILTER(Dates,
            Dates[Date] > StartFinish[Start Date] && 
            Dates[Date] < StartFinish[Finish Date]),
        IF(WEEKDAY(Dates[Date]) = 1 || WEEKDAY(Dates[Date]) = 7, 1, 0)
    )

If you have an IsWorkDay column in your calendar table (which might include holidays as well as weekends), you can just reference that for the last line instead:

        IF(Dates[IsWorkDay], 0, 1)

Note that this approach assumes a working day is 24 hours rather than a more standard 8 hours. You'll have to make some adjustments if you don't want the fractional part to indicate the portion of 24 hours. To switch to a portion of 8 hour work days, just multiply the fractional part by 24/3 = 3.

In DAX, can I create a function that calculates the working days between two date/time fields?

1 Answers