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

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