Forum Discussion

richardmayo's avatar
richardmayo
Helper II
6 years ago
Solved

DATEDIFF excluding weekends

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)  ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

danextian's avatar
danextian
Super User
6 years ago

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 )

 

 

 

JenWilson's avatar
JenWilson
Helper II
1 year ago

this works great for me, but I doesn't show negative values. What can I add to the code to show negative values?