Avg days open for open tickets only_3 mth rolling

[Juzdeppe]

Hello Everyone,
I hope you can help me with my topic.

I've been trying to formulate a solution to one problem for some time now. I want to count avg days open for tickets in system for individual weeks. However, I am not talking about the result of the average time for closed tickets, but for those that were still open in a given week: (Sum of the days open of all open tickets) / (Count of open tickets). Time is calculated from date ticket is entered in the system to date of calculation. 

The data I work on is updated daily. So a ticket that was still open one day may already have a closing date the next day.
Then given the average days open result for each week, I need to count the 3 month rolling result (last 12 weeks).

Sample table with data. If the "Close Date" is blanc then the ticket is still open:

Create Date	Ticket Number	Close Date
07.03.2022	80159740	30.06.2023
19.05.2022	80160647
29.06.2022	80161057	04.04.2023
28.06.2022	80161081	23.05.2023
04.07.2022	80161108
25.07.2022	80161389	23.05.2023
09.08.2022	80161686	04.04.2023
19.08.2022	80161792
25.08.2022	80161902	19.04.2023
12.09.2022	80162016	25.04.2023
13.09.2022	80162017	24.04.2023
13.10.2022	80162496
13.10.2022	80162498	03.04.2023
14.10.2022	80162528	18.04.2023
01.11.2022	80162756
11.11.2022	80162935	12.04.2023
11.11.2022	80162936	12.04.2023
22.11.2022	80163093	23.05.2023

I will be very grateful for your help!

[johnt75]

You could create a couple of measures like

Weekly avg open cases =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR OpenTickets =
    CALCULATETABLE (
        VALUES ( 'Table'[Ticket ID] ),
        'Table'[Create date] <= ReferenceDate,
        ISBLANK ( 'Table'[Close date] )
            || 'Table'[Close date] > ReferenceDate
    )
VAR NumDaysOpen =
    CALCULATE (
        SUMX (
            'Table',
            DATEDIFF (
                'Table'[Create date],
                COALESCE ( 'Table'[Close date], ReferenceDate ),
                DAY
            )
        ),
        OpenTickets
    )
VAR NumOpenTickets =
    COUNTROWS ( OpenTickets )
VAR Result =
    DIVIDE ( NumDaysOpen, NumOpenTickets )
RETURN
    Result


3 month average =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    CALCULATETABLE (
        VALUES ( 'Date'[Week commencing] ),
        DATESBETWEEN ( 'Date'[Date], ReferenceDate - 84, ReferenceDate )
    )
VAR Result =
    AVERAGEX ( DatesToUse, [Weekly avg open cases] )
RETURN
    Result