Calculating Duration Between Two Dates Not Including Weekends and Holidays

[JDopson]

While calculating the difference between orderdate and shipdate, how can i tell the formula to not to could saturdays and sundays. IE ordered friday, shipped monday. would be one day.

i thought this solution worked but it has not.

[JDopson]

here is a link to my file. i am working in sales header B
Ship date and time minus order date and time, IGnoring all weekends and holidays

https://drive.google.com/drive/folders/1P0_5c8OZQAp3dpwPDTgVF9NWNvl4k_iQ?usp=sharing

[EnterpriseDNA]

Hi, just bumping this post for visibility.

[Harsh]

Hello @JDopson,

Thank You for posting your query onto the Forum.

Just few days back I encountered this very similar type of problem onto the Forum. The only difference being is, in your current query you also have the “Time” aspect whereas the previous one was only limited to the “Date” aspect.

So now, since you’ve a comination of “Date and Time” within your columns in the table. The first thing is, to separate them i.e. have individual columns for date as well as for time so that time-intelligence calculations becomes much more easier and more importantly it reduces the cardinality and thereby optimizes the data model as well. Below is the screenshot of the revised data table provided for the reference -

Now, once your data table is sorted, it’s time to write the measure. Since your conditions are like - “Weekends” and “Holidays” should be excluded and “Ordered friday, Shipped monday would be considered as one day”. Below is the measure alongwith the screenshot of the final results provided for the reference -

Duration Between Order and Ship Date - 1 = 
VAR _Days_Calculation =
CALCULATE( COUNTROWS( 'Date Table' ) , 
    DATESBETWEEN(
        'Date Table'[Date] , 
        SELECTEDVALUE( Data[Order Date] ) , 
        SELECTEDVALUE( Data[Ship Date] ) ) , 
    'Date Table'[DayOfWeekName] <> "Saturday" ,
    'Date Table'[DayOfWeekName] <> "Sunday" , 
    'Date Table'[Holiday] = BLANK() ,
    ALLSELECTED( Data ) ) - 1

VAR _Time_Calculation =
FORMAT( 
    SELECTEDVALUE( Data[Order Time] ) - SELECTEDVALUE( Data[Ship Time] ) , 
    "HH:MM:SS" )

VAR _Minutes_Calculation = 
ABS(
    DATEDIFF(
        SELECTEDVALUE( Data[Order Time] ) , 
        SELECTEDVALUE( Data[Ship Time] ) , 
        MINUTE ) )

VAR _Results = 
SWITCH( TRUE() , 
    _Days_Calculation > 1 && _Minutes_Calculation > 60 , 
        _Days_Calculation & " Days " & _Time_Calculation & " Hours" ,

    _Days_Calculation > 1 && _Minutes_Calculation <= 60 , 
        _Days_Calculation & " Days " & _Time_Calculation & " Minutes" ,

    _Days_Calculation <= 1 && _Minutes_Calculation > 60 , 
        _Days_Calculation & " Day " & _Time_Calculation & " Hours" ,

    _Days_Calculation <= 1 && _Minutes_Calculation <= 60 , 
        _Days_Calculation & " Day " & _Time_Calculation & " Minutes" ,
    
BLANK() )

RETURN
_Results

I’m also attaching the working of the PBIX file as well as providing the link below of the post pertaining to the similar query that was tackled on to the forum for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Time To Pick Test - Harsh.pbix (8.5 MB)

Real Working Days PART 2!

Hello @cms418, Since I ran out of characters/word space here’s the additional or important points mentioned below pertaining to the solution provided above - Important Notes: 1). In the case of blank end date, the start date itself is considered as a end date. You can modify the logic as per your business scenario/requirements. 2). Wherever Start Date or End Date of the previous criteria and the End Date of the new criteria is same in that case they’re still considered as over-lapping in my …

[JDopson]

Hello. Thanks for looking at this for me. i imported your formula to my data model. For tickets on the same day i am getting a -1 on the day? see the JPG I posted

[JDopson]

I figured it out! my date table wasn’t marked as a date table.
works great now thanks

[Harsh]

Hello @JDopson,

You’re Welcome.

I’m glad that I was able to assist you.

The “Time” aspect in your data model made this query even more interesting.

Thanks and Warm Regards,
Harsh

[Greg]

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.

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.

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.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

CONVERT function (DAX) - DAX

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

[JDopson]

Sorry Greg,
I reposted a pibx with my data in it. This is still giving me trouble, as i cant use the the results in a formula to find the average time so far. I was thinking of converting to seconds summarizing and then converting back to hours and minutes.

[JDopson]

How would i be able to calculate the average pick ours per order?

[Harsh]

Hello @JDopson,

Was this query part of your original query? If not then can you please create a new thread and post the details about how would you to like to see the results alongwith the reference files as necessary Memebers of the forum will look into your query and will provide the solutions accordingly.

As per the forum guidelines, one question can be posted in a single thread. Please go through the forum guidelines for the reference. For tangential questions, always create a new thread rather than following up onto the same one.

Thanks and Warm Regards,
Harsh

[JDopson]

Thanks
I don’'t recall if it was.