DATEDIFF excluding weekends

[richardmayo]

Ive had a look at existing threads but none seem to be as simple as my scenario.

This is my formula in a measure

Turnaround (Days) = DATEDIFF('Jobs'[date_entered],'Jobs'[exported_date],DAY)

I want to basically exclude Sat and Sun from the calculation.

Help

[JuNu_MDJ]

Please replace CMS_Detail[Reported_Date] and CMS_Detail[Completed_Date] as your start date and end date.

Dates_between =
VAR TEMP_CALENDAR =
    FILTER (
        ADDCOLUMNS (
            CALENDAR (
                CMS_Detail[Reported_Date],
                COALESCE ( CMS_Detail[Completed_Date], TODAY () )
            ),
            "DAY_NAME", WEEKDAY ( [Date], 1 )
        ),
        [DAY_NAME] < 6
    )
RETURN
    COUNTROWS ( TEMP_CALENDAR )

Ping me, in any case, Junaise PT 

Did I answer your question? Mark my post as a solution!

[v-yiruan-msft · Accepted solution]

Hi @richardmayo,

You can follow the below steps to get the number of days between date_entered and exported_date:

1. Create one calendar table with normal date if your data model still not have any date table   

2. Add one calculated column on table Jobs with the below formula:        

Turnaround (Days) = CALCULATE(COUNTROWS('Calendar'),filter('Calendar',WEEKDAY('Calendar'[Date],2)<6),DATESBETWEEN('Calendar'[Date],'Jobs'[date_entered],'Jobs'[exported_date]))

Best Regards

Rena

[MdJ83]

Great, how can you take it forward to also exclude public holidays

[Anonymous]

Thank you! Perfect and simple solution!

[richardmayo]

Yes this is perfect (and does not require a new table as I already had a date table)

Also, I just added a -1 at the end of the formula so that it does not count the start date in the calculation of "turnaround".

[amitchandak]

refer this file(Page 2), I have created datediff, without weekend https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[danextian]

Hi @richardmayo 

Create a separate and disconnected (no relationship) calendar table that has an indicator whether a date is a weekday or not. Example:

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
    "Day Name", FORMAT ( [Date], "ddd" ),
    "Weekday", IF ( WEEKDAY ( [Date], 2 ) >= 6, 0, 1 )
)

Then create this column:

Datediff =
VAR __Datediff =
    CALCULATE (
        SUM ( 'Dates'[Weekday] ),
        DATESBETWEEN ( 'Dates'[Date], 'Jobs'[date_entered], 'Jobs'[exported_date] )
    ) - 1
RETURN
    IF ( __Datediff < 0, 0, __Datediff )