Calculate working days to resolution, when not in suspended status

[Anonymous]

I'm trying to obtian the average business days (Monday - Friday) to resolution and longest amount of resolution time (time it took to close the ticket that stayed open the longest).  I've worked on this for quite some time, but am at my wits end and running out of time.  Can someone please help?

My date table contains an "IsWorkDay" (0 or 1) column and my Status table contains the facts related to the Ticket (please see screenshot below)

I'm trying to find the best way to determine the number of business days it took to resolve each ticket, excluding the days the ticket was in a "Suspend" status. If the ticket isn't resolved, I don't want it included in the results.  From there, I'll create a card visual to get the average number of days it took to close tickets and another card for the number of days it took to resolve the ticket that was opened the longest. 

For example, Ticket 1 should be 28 days to resolution  (37 total business days - 9 business days in a suspended state)

I can't share my pbix due to confidental data but if  I can provide any other information, please let me know.

Thank you!!

[v-yuta-msft]

@Anonymous ,

Could you share some sample data and clarify more details about your requirement?

Community Support Team _ Jimmy Tao

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