PowerBI e históricos de cambios de estado

Contexto

Para tener un mayor conocimiento sobre el desempeño del departamento técnico de RETAbet, cada mes publicamos una foto del trabajo en curso (WIP¹) del departamento y su evolución desde que recogemos estos datos. Lo publicamos, junto con otros KPIs², en PowerBI.

Problema

Nuestro WIP, en el desarrollo de software, lo medimos por la cantidad de tareas que sacamos del backlog y no están terminadas. Cada una de esas tareas pasa por varios estados: Backlog, En curso, Test, Done.

Cada vez que cargamos los datos en PowerBI, obtenemos la foto del WIP actual, pero en cualquier otro momento en que refresquemos los datos, perdemos el valor anterior porque los estados de las tareas han cambiado. Por lo que, si queremos mostrar el histórico del WIP, tenemos que guardar “a mano”, en una tabla del propio archivo de PowerBI, el valor actual del WIP en la fecha en la que se ha recogido la información.

Objetivo

Automatizar los KPIs para que muestren históricos basados en los estados (WIP, Backlog, Work Distribution) y poder consultarlos en cualquier momento sin tener que realizar operaciones manuales.

Opciones

Después de estudiar estas alternativas:

• PowerBI extrayendo todos los cambios de estado
• Almacenes de datos incrementales en PowerBI
• ETL/SSIS/SQL/PowerBI
• Construir mi propia aplicación ETL/SQL/PowerBI

Me decidí por la última, sopesando infraestructura, curva de aprendizaje y mantenibilidad.

Manos a la obra

1. Construir una aplicación ETL para extracción, transformación y carga de datos de las tareas y cambios de estado en el SQL ✅
2. Definir la carga y transformación de los datos en PowerBI ✅
3. Crear Relaciones/Modelo en PowerBI ✅
4. Mostrar los datos 🥺

Entre los datos que representan el cambio de estado de una tarea se guarda el momento de cambio de estado del elemento, pero no el estado concreto de cada elemento en una fecha F. Con lo cual, para mostrar el estado del sistema en una fecha F, hay que contar con los elementos que hayan cambiado al estado que se quiere estudiar antes de F y que en F siguen en ese estado.

En mi primera aproximación tenía datos incorrectos, porque sólo analizaba los cambios de estados que habían tenido lugar cada día, olvidando los previos.

En una tabla como la anterior, si calculaba el WIP del día 2, obtenía sólo dos resultados correspondientes a los cambios de estado (Issues 2 y 3), cuando el valor correcto es 3 tareas en WIP (Issues 1, 2 y 3). Esto mismo sucedía con el día 3 – obtenía como resultado que no había tareas en WIP, cuando el valor correcto es 3 tareas (Issues 2, 3 y 4)

De hecho, si en un día no se daban cambios de estado, éste no aparecía en la gráfica. Esta situación y la colaboración de una colega³ bastante ducha en estos temas nos hizo ver la necesidad de incorporar un calendario.

Me construí una tabla de calendario en PowerBI para tener todos los días del año:

CalendarM = 
VAR StartDate = DATE(2017, 1, 1)   -- Set your desired start date here
VAR EndDate = DATE(2024, 12, 31)   -- Set your desired end date here
RETURN
    CALENDAR(StartDate, EndDate)

Y después de muchas pruebas, preguntas a colegas, a IAs, a StackOverflow⁴, conseguí mostrar los datos. En la propia tabla de calendario, agregué una medida para el WIP que calcula:

1. Para cada día X
2. Las tareas que tengan cambios de estado a WIP antes de X
3. Y que no hayan cambiado a otro estado o que el cambio de estado se haya producido después de X

Aquí os dejo la fórmula por si tenéis un problema parecido.

HistoricalWIP = 
VAR SelectedDate = MAX(CalendarM[Date]) -- Obtiene la fecha seleccionada en el segmento de datos
VAR InProgressOnSelectedDate = FILTER(
            StatusChanges,
            StatusChanges[ChangeDate] <= SelectedDate
            && (
                ISBLANK(StatusChanges[UntilDate]) || StatusChanges[UntilDate] > SelectedDate
            )
            && (
                StatusChanges[_FinalStatusByIssueId] = "In Progress"
                || StatusChanges[_FinalStatusByIssueId] = "Blocked"
                || StatusChanges[_FinalStatusByIssueId] = "Test"
            )
        )
VAR GroupedByIssueId = SUMMARIZE(InProgressOnSelectedDate, StatusChanges[IssueId])
RETURN COUNTROWS(GroupedByIssueId)

Resultado

Notas

Los resultados son los correctos, pero después de obtenerlos me he dado cuenta de que, aunque posible sacar un histórico como este de una métrica, el proceso es bastante pesado para PowerBI ya que por cada fecha del calendario (Código de CalendarM, más arriba), PowerBI tiene que revisar todos los cambios de estado que se han producido desde el inicio del calendario y filtrarlos. Mientras más amplio sea el rango de fechas que mostremos en el gráfico, más tardará en recalcular todo el histórico.

También es importante que el filtro de fechas sólo se use para la ventana de tiempo del gráfico, no para filtrar los cambios de estado. No debemos relacionar el calendario (CalendarM) con la tabla de cambios de estado para evitar ignorar datos importantes para la construcción del histórico.

Si el proceso de construcción de un histórico es pesado, tenemos que considerar que, si queremos mostrar el histórico de varias métricas, cada vez que se actualicen los datos, PowerBI tardará un rato hasta mostrar los datos.

Es por eso que me he planteado sacar la construcción de históricos del PowerBI y llevarla al ETL y después de la carga de todos los datos lanzar un proceso de construcción de históricos. Eso permitirá visualizar los datos de forma rápida y trasladar el procesamiento a un entorno más potente.

Referencias

¹WIP: https://medium.com/@hamrinmarcus/what-is-wip-and-when-is-it-a-problem-c44f32fc745

²KPI: https://www.indeed.com/career-advice/career-development/key-performance-indicators

⁴Cristina: https://www.linkedin.com/in/cristina-tarabini-castellani

⁴StackOverflow: https://stackoverflow.com/questions/79208066/how-to-show-on-a-line-chart-in-powerbi-the-sum-of-items-that-are-in-a-specific-s