Cálculo del total móvil mensual para los tickets abiertos y cerrados

[Syndicate_Admin]

Hola a todos
Estoy buscando ayuda para resolver el siguiente problema:

Ticket_ID	Open_Date	Closed_Date
1	01/01/2020	01/01/2020
2	01/12/2021	05/12/2021
3	01/13/2021	01/14/2021
4	01/14/2021	01/15/2021
5	01/15/2021	01/16/2021
6	01/16/2021	01/17/2021
7	01/17/2021	01/18/2021
8	01/18/2021	01/19/2021
9	01/19/2021	01/20/2021
10	03/20/2022	03/21/2022
11	03/21/2022	03/22/2022
12	03/22/2022	03/23/2022

Del conjunto de datos anterior,
1.Necesito calcular el total móvil mensual para los tickets en estado abierto
2. Calcula el total móvil mensual de los tickets en estado Cerrado
3. También necesito trazar los totales móviles mensuales para boletos abiertos y cerrados en el gráfico de líneas de ejes múltiples.

Gracias de antemano.

[Syndicate_Admin]

Así es como lo hice:

Crear una tabla de fechas:

DateTable = CALENDAR(DATE(2020,1,1), DATE(2022,12,1))

Cree una columna calculada en esa tabla. Esto es solo para que pueda dividir por mes (formateado como AAAA Mmm):

YYYY Mmm = FORMAT('DateTable'[Date], "Mmm YYYY")

Crear tres medidas para calcular el número de tickets abiertos dentro de un período determinado; el número de entradas cerradas en un plazo determinado; y el número de entradas que siguen abiertas durante un período determinado.

TicketCount (Opened in Period) = 
    VAR _StartDate = FIRSTDATE('DateTable'[Date])
    VAR _LastDate = LASTDATE('DateTable'[Date])

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            'TicketData'[Open_Date] >= _StartDate,
            'TicketData'[Open_Date] <= _LastDate
        )

TicketCount (Closed in Period) = 
    VAR _StartDate = FIRSTDATE('DateTable'[Date])
    VAR _LastDate = LASTDATE('DateTable'[Date])

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            'TicketData'[Closed_Date] >= _StartDate,
            'TicketData'[Closed_Date] <= _LastDate
        )

TicketCount (Open During Period) = 
    VAR _StartDate = FIRSTDATE('DateTable'[Date])
    VAR _LastDate = LASTDATE('DateTable'[Date])

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            _StartDate <= 'TicketData'[Closed_Date],
            'TicketData'[Open_Date] <= _LastDate
        )

Para obtener sus datos de muestra, agregué una tabla calculada llamada TicketData:

TicketData = 
    DATATABLE(
        "TicketID", INTEGER, "Open_Date", DATETIME, "Closed_Date", DATETIME,
        {
            {"1","01/01/2020","01/01/2020"},
            {"2","01/12/2021","05/12/2021"},
            {"3","01/13/2021","01/14/2021"},
            {"4","01/14/2021","01/15/2021"},
            {"5","01/15/2021","01/16/2021"},
            {"6","01/16/2021","01/17/2021"},
            {"7","01/17/2021","01/18/2021"},
            {"8","01/18/2021","01/19/2021"},
            {"9","01/19/2021","01/20/2021"},
            {"10","03/20/2022","03/21/2022"},
            {"11","03/21/2022","03/22/2022"},
            {"12","03/22/2022","03/23/2022"}
        }
    )

Y esas medidas parecen funcionar cuando las dejo caer en una tabla (por mes)

Y cuando se grafica por mes:

Espero que esto ayude.

[Syndicate_Admin]

Me acabo de dar cuenta de que estabas buscando totales mensuales. Aquí hay tres medidas que creo que lograrán esto:

Monthly Rolling Total (Opened) = 
    // Number of tickets whose Opened_Date was in the last 30 days prior to the current day
    VAR _LastDate = LASTDATE('DateTable'[Date])
    VAR _StartDate = DATEADD(_LastDate, -30, DAY)

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            'TicketData'[Open_Date] >= _StartDate,
            'TicketData'[Open_Date] <= _LastDate
        )

Monthly Rolling Total (Closed) = 
    // Number of tickets whose Closed_Date was in the last 30 days prior to the current day
    VAR _LastDate = LASTDATE('DateTable'[Date])
    VAR _StartDate = DATEADD(_LastDate, -30, DAY)

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            'TicketData'[Closed_Date] >= _StartDate,
            'TicketData'[Closed_Date] <= _LastDate
        )

Monthly Rolling Total (Open) = 
    // Number of tickets whose status was Open in the last 30 days prior to the current day
    VAR _LastDate = LASTDATE('DateTable'[Date])
    VAR _StartDate = DATEADD(_LastDate, -30, DAY)

    RETURN
        CALCULATE(
            COUNTROWS('TicketData'),
            _StartDate <= 'TicketData'[Closed_Date],
            'TicketData'[Open_Date] <= _LastDate
        )

[Syndicate_Admin]

Gracias por la solución, pero esto no funciona cuando la columna de fecha tiene valores duplicados.

Por ejemplo: cuando tengo 2 tickets para el mismo Open_Date, la solución anterior falla.

¿Puede ayudar a resolver este escenario?

Gracias de antemano.

[Syndicate_Admin]

@Sattam ¿Podría publicar los datos de ejemplo que muestran que se produce este problema? He agregado una fila adicional de datos a los datos de muestra originales que proporcionó para que contengan una fecha duplicada y las medidas parezcan devolver el número correcto. Los TicketIDs 12 y 13 tienen el mismo Open_Date y Close_Dates.

Cuando creo una tabla visual con Fecha y el Total Móvil Mensual (Abierto), para el 22/03/2022 obtengo el recuento correcto de 4 tickets (los ID de ticket 10, 11, 12 y 13 tienen un Open_Date del 20/02/2022 al 22/03/2022)

TicketData = 
    DATATABLE(
        "TicketID", INTEGER, "Open_Date", DATETIME, "Closed_Date", DATETIME,
        {
            {"1","01/01/2020","01/01/2020"},
            {"2","01/12/2021","05/12/2021"},
            {"3","01/13/2021","01/14/2021"},
            {"4","01/14/2021","01/15/2021"},
            {"5","01/15/2021","01/16/2021"},
            {"6","01/16/2021","01/17/2021"},
            {"7","01/17/2021","01/18/2021"},
            {"8","01/18/2021","01/19/2021"},
            {"9","01/19/2021","01/20/2021"},
            {"10","03/20/2022","03/21/2022"},
            {"11","03/21/2022","03/22/2022"},
            {"12","03/22/2022","03/23/2022"},
            {"13","03/22/2022","03/23/2022"}
        }
    )

[Syndicate_Admin]

@Sattam Espero que esto ayude