Calculating Duration Between Two Dates Not Including Weekends and Holidays

DAX DAX Calculations
4

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 -

Revised Data Table
Revised Data Table1156×812 80.4 KB

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

Final Results
Final Results929×788 53.8 KB

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. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

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

1 Like