Calculate days to resolution, excluding days in Status, weekends and holidays

[Anonymous]

I have been searching for a solution for a few days now, but have been unable to figure this out.

I have a date table that contains an IsWorkDay flag (0 or 1).

I also have a Status table (Please see sample below)....

I need to calculate the following items:

1. The number of days each ticket spent in a Status (excluding weekends & Holidays)

2. The number of days each ticket took to close (Open date to Resolution Date), excluding weekends, holidays and the number of days it spent in a "Suspended" status. This is only for the tickets that are currently in a "Closed" or "Solution provided" status. (Ultimately, I'm trying to calculate the average number of days it takes to close tickets.... without weekends and days in "suspended")

3. Longest amount of days it took to resolve a ticket (again, without weekends, holidays or days spent in a "suspended" status and only for tickets that are "Closed" or "Solution Provided")

4. For tickets that are not closed or in a solution provided status, I need to calculation the number of days the ticket has been open (excluding weekends, holidays and the number of days it spent in a "Customer Research" status).

5. Determine if a ticket was in a "Solution Provided" status and then entered any status except for "Closed" or "Canceled". (I'm trying to get a count of how many tickets have done this, and how often this happens)

I had played around with a holiday table, but wasn't sure of the best way to implement it (we only have 8 holidays a year - and they change each year)...

I know this is a lot - and I do have bits and pieces of it calculated, but I'm pretty sure there is a much more efficient way to tie all this together.  Thank you in advance for you help! It is greatly appreciated.

[v-danhe-msft]

Hi @Anonymous ,

Could you please post your desired result and upload your pbix file if possible that I could have a test for you?

Regards,

Daniel He

[Anonymous · Accepted solution]

Sorry, I'm unable to provide my pbix file.  I think I've got a big part of it figured out now.  I appreciate your help\response.