# Calculating Duration Between Two Dates Not Including Weekends and Holidays

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.

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

Hi, just bumping this post for visibility.

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)

1 Like

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

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

1 Like

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

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

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

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.

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

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

Thanks
I donâ€™'t recall if it was.