topic How to calculate average working days of closed tickets? in DAX Commands and Tips

How to calculate average working days of closed tickets?

Anonymous — Wed, 23 Sep 2020 07:10:36 GMT

So, I am new to Power BI and my database is a bit weird. it looks like this:

```
Ticket Open Date Close Date
BR001 02/01/2020 02/01/2020
BR001 02/01/2020 10/01/2020
BR001 12/02/2020 14/02/2020
BR002 02/02/2020 04/02/2020
BR002 05/02/2020
```

The reason it looks like this is that because some tickets are reopened up to 5-6 times. If the last row of the ticket does not contain a closing date, it means the ticket is still open for the time being. The result that would be ideal to me (I don't know if it's even possible) is to have 8 working average days for the first ticket in a matrix or table (excluding weekends) and not taking into account the second ticket since it's still open. It should be 8 because the first line is one day, the 2nd January, then from the second till the tenth it should be 8 days, but on the calendar there are 2 days weekends, so it's technically 6 days. Since the first line started with the 2nd of January and ended the same day, and the second line started with the 2nd again, it should be counted as one day, not two. Then finally there are the two days in February which makes a total of 8 days. I hope that's clear enough.

The desired output should look something like:

```
Ticket Average working days
BR001 8 Days
BR002 null
```

Is it possible to have a result like that? Or what would be the closest thing to it? I am also open to transform the data with M-Query.

Thanks a lot in advance!

Re: How to calculate average working days of closed tickets?

Anonymous — Wed, 23 Sep 2020 07:23:40 GMT

That's not as hard as one might think. You just have to expand the intervals into real days, one by one, for each ticket and take the DISTINCT days. Once you have such a table, get rid of the weekends from it and voila!, you've got what you need. Then the working days will be the count of the days in such a table. Of course, the same technique works for Power Query as well as DAX. And it can be made dynamic, if you don't want to transform your table.

Re: How to calculate average working days of closed tickets?

Anonymous — Wed, 23 Sep 2020 08:40:36 GMT

Thanks a lot for your suggestion! But is it possible if you show me how in detail? Maybe with screenshots or a sample file? As I said, I am very new to Power BI and I still don't know how everything works

Re: How to calculate average working days of closed tickets?

Anonymous — Wed, 23 Sep 2020 10:24:42 GMT

I don't have the time to do it in detail and I can't post files as I'm at work and have restrictions. You should create a table with expanded intervals. Instead of
BR001 02/01/2020 02/01/2020
BR001 02/01/2020 10/01/2020
you should create
BR001 02/01/2020
BR001 03/01/2020
BR001 04/01/2020
...
BR001 10/01/2020
Can you see it? If you expand the intervals into separate days and then take only the distinct combinations of (ticket, day), you can easily get rid of weekends as well from this and then for each ticket you've got the effective days. What you then have to do is to count them.