How to calculate average working days of closed tickets?

[Anonymous]

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!

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.