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)

 

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

 

Help

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • richardmayo's avatar
      richardmayo
      Helper II
      6 years ago

      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".

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you! Perfect and simple solution!

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • 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
      2 years ago

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

       

  • JuNu_MDJ's avatar
    JuNu_MDJ
    New Member
    3 years ago

    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!