I know this question has been asked a bunch of times before, but I think I have a unique nuance to the question. We use Zendesk in our business and I am trying to calculate the average number of work days it takes between when a ticket is created and when it’s solved. Based on this video by Brian, I was able to come up with this script:
Avg Work Days To Solve = AVERAGEX( 'Zendesk Ticket Metrics', Calculate( COUNTROWS( 'Date Table' ), DATESBETWEEN( 'Date Table'[Date], 'Zendesk Ticket Metrics'[Ticket Created Datetime], 'Zendesk Ticket Metrics'[Solved Datetime wUnsolved]), FILTER( 'Date Table', 'Date Table'[DayOfWeekName] <> "Saturday" && 'Date Table'[DayOfWeekName] <> "Sunday" ), ALLSELECTED( 'Zendesk Ticket Metrics' ) ) )
When I export the data to Excel and do my own calculations, the above creates the exact same result as subtracting the two dates in Excel and adding 1. However, if I were to use the NETWORKDAYS function in Excel, it results in a lower average than what the above DAX formula returns. My hypothesis, therefore, is that I am not correctly removing “Saturday” and “Sunday” in the DAX formula.
Any help would be greatly appreciated.