Number of days between date columns excluding weekends

[pears980]

# of Days = DATEDIFF('EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[CREATION_DATE],'EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[LAST_UPDATE_DATE],DAY)

This if formula i have cant seem to figure out how to tell it to not count weekends.

[v-stephen-msft · Accepted solution]

Hi @pears980 ,

To calculate the number of days excluding weekends, you can create a calendar table first. No relationship between the calendar and the main table.

Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

Then create a measure

# of Days = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar',[Date]>=MAX('Table'[StartDate])&&[Date]<=MAX('Table'[EndDate])&&[Weekday]<>6&&[Weekday]<>7))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @pears980 

You need to set up a date table which can be used to see what dates are Sat or Sun.  You can then write something like this

# of Days = DATEDIFF('EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[CREATION_DATE],'EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[LAST_UPDATE_DATE],DAY) - ABS(CALCULATE(COUNTROWS('DateTable'[Date]), FILTER('DateTable', 'DateTable'[Date] <= 'EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[CREATION_DATE] && 'DateTable'[Date] >= 'EAM-XXPXD-XXPXD_EAM_ZOHO_WORKREQUEST'[LAST_UPDATE_DATE] && DAY('DateTable'[Date]) <> 1 && DAY('DateTable'[Date]) <> 7)))

NOTE: My PC is playing up and Power BI won't start so I haven't tested that code yet.  Will reboot then test and edit as required.

Regards

Phil