Calculating Duration Between Two Dates Not Including Weekends and Holidays

DAX DAX Calculations
8

Hi @JDopson.

Here’s another take on your issue.

I’d been wanting to take a deep dive into datetime values for a while, and your post gave me the opportunity, and here’s what I found:

Power BI stores dates and times as a number representing the number of days since 1899-Dec-30, plus a fractional portion of a 24 hour day: dddd.tttttt, also known as a serial date-time.

  • The portion to the left of the decimal point are integer days.
  • The portion to the right of the decimal point are decimal days.

eDNA Forum - Duration Between 2 Dates-2
eDNA Forum - Duration Between 2 Dates-2789×583 56.7 KB

Using the serial datetimes, I created a new measure as follows:

Duration Between Order and Ship Date - Greg = 
VAR _OrderDT = SELECTEDVALUE( Orders[Order Date & Time] )
VAR _ShipDT = SELECTEDVALUE( Orders[Ship Date & Time] )
VAR _FullInterval = CONVERT(_ShipDT, DOUBLE) - CONVERT( _OrderDT, DOUBLE)
VAR _NonBusinessDaysInInterval = CALCULATE( COUNTROWS( Dates ),
    Dates[Date] > _OrderDT,
    Dates[Date] < _ShipDT,
    Dates[IsBusinessDay] = FALSE()
    )
VAR _All = _FullInterval - _NonBusinessDaysInInterval
VAR _LeftOfDecimal = INT( _All )
VAR _RightOfDecimal = _All - _LeftOfDecimal
VAR _Days = _LeftOfDecimal
VAR _HHMMSS = FORMAT( _RightOfDecimal, "HH:MM:SS" )
VAR _Result = _Days & "." & _HHMMSS

RETURN
_Result

For the most part, this alternate take on datetime values gives the same results as @Harsh’s, but there are some differences; I ran out of time before I could identify the cause of the discrepancy, but thought I would post anyway.

eDNA Forum - Duration Between 2 Dates-1
eDNA Forum - Duration Between 2 Dates-11541×1312 414 KB

I’ll hopefully investigate further at a later date when I have another opportunity to evaluate time between 2 dates.
Greg

eDNA Forum - Duration Between 2 Dates.pbix (14.6 MB)

P.S.: Also, your example was data only and didn’t have any visuals; I strongly recommend giving it your best effort before submitting a question, and be sure to include not only the PBIX but your source data files as well. As your submission did not include the source data files, I used DAX Studio to extract your data, then re-imported it. Your dates table did not have weekday, weekend, or holiday flags so I replaced your dates table with the Enterprise DNA Extended Date Table, marked the [Dates] table as a date table, and updated your code accordingly.

Edit: tested and corrected serial datetime “zero”; added test image. Thanks to @AntrikshSharma for the catch.

2 Likes