Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos)

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos)

Imagina que tienes un modelo de datos con tres tablas: Ventas, Pedidos y Devoluciones. Ventas arranca en enero de 2022. Pedidos, en marzo de 2020. Devoluciones, en julio de 2021.

Creas un calendario que empieza el 1 de enero de 2022 porque es la fecha de la tabla más grande. Montas la tabla dinámica y todo parece correcto. Hasta que alguien pregunta por los pedidos de 2020 y 2021. No aparecen. Quedan fuera porque el calendario no los cubre.

El problema no es la tabla dinámica. Es que el calendario no alcanza todas las filas de todas las tablas de hechos.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 1)

Por qué un modelo con varias tablas de hechos necesita un calendario común

Cuando trabajas con un modelo de datos en Excel, cada tabla de hechos (ventas, pedidos, devoluciones, facturas) tiene su propia columna de fecha. Todas esas columnas se relacionan con una única tabla calendario.

Esa tabla calendario debe empezar en la fecha mínima de todas las tablas y terminar en la fecha máxima de todas. Si empieza más tarde, dejas filas sin fecha. Si termina antes, cortas el análisis. Si te pasas de largo, inflas el modelo sin motivo.

Hacerlo a ojo funciona la primera vez. Pero cada cierre trimestral añades filas nuevas. El calendario se queda corto o sobra. Y no te enteras hasta que alguien pregunta por un dato que no aparece.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 2)

Cómo calcular el rango del calendario desde Power Query

La solución es calcular el mínimo y el máximo de todas las tablas de hechos y generar el calendario entre esos dos extremos. Power Query lo hace en unos pocos pasos.

Primero, creas una consulta que obtiene la fecha mínima de cada tabla:

let
    MinVentas = List.Min(Ventas[Fecha]),
    MinPedidos = List.Min(Pedidos[Fecha]),
    MinDevoluciones = List.Min(Devoluciones[Fecha]),
    FechaInicio = List.Min({MinVentas, MinPedidos, MinDevoluciones})
in
    FechaInicio

Después, haces lo mismo con la fecha máxima:

let
    MaxVentas = List.Max(Ventas[Fecha]),
    MaxPedidos = List.Max(Pedidos[Fecha]),
    MaxDevoluciones = List.Max(Devoluciones[Fecha]),
    FechaFin = List.Max({MaxVentas, MaxPedidos, MaxDevoluciones})
in
    FechaFin

Con esas dos fechas, generas el calendario:

let
    FechaInicio = ...,
    FechaFin = ...,
    NumeroDias = Duration.Days(FechaFin - FechaInicio) + 1,
    ListaFechas = List.Dates(FechaInicio, NumeroDias, #duration(1,0,0,0)),
    TablaCalendario = Table.FromList(ListaFechas, Splitter.SplitByNothing(), {"Fecha"}, null, ExtraValues.Error)
in
    TablaCalendario

Ahora cada vez que actualices el modelo, Power Query recalcula el mínimo y el máximo. El calendario crece o se ajusta solo.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 3)

Qué pasa si una tabla tiene fechas futuras

Supón que la tabla de Pedidos incluye pedidos programados para dentro de seis meses. El MAX de Pedidos será una fecha futura. El calendario se extenderá hasta esa fecha.

Eso es correcto. Si tienes pedidos programados, necesitas que el calendario los cubra. Si no los quieres en el análisis, filtra la tabla de Pedidos antes de calcular el MAX, no el calendario.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 4)

Qué pasa si una tabla está vacía

Si una de las tablas de hechos está vacía, List.Min y List.Max devuelven error. Para evitarlo, puedes comprobar si la tabla tiene filas antes de calcular el mínimo y el máximo, o usar try … otherwise para manejar el error.

Otra opción es excluir esa tabla del cálculo hasta que tenga datos. Depende de si la tabla vacía es temporal o permanente.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 5)

Qué columnas añadir al calendario

Una vez que tienes la tabla con la columna Fecha, añades las columnas derivadas que necesites: Año, Mes, Trimestre, Día de la semana, Número de semana, Mes-Año.

Power Query tiene funciones para todas:

  • Date.Year([Fecha])
  • Date.Month([Fecha])
  • Date.QuarterOfYear([Fecha])
  • Date.DayOfWeek([Fecha])
  • Date.WeekOfYear([Fecha])

Si necesitas un formato concreto para Mes-Año (por ejemplo, «2024-11»), usa Date.ToText([Fecha], «yyyy-MM»).

Añade solo las columnas que uses en las tablas dinámicas. Cada columna extra ocupa memoria.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 6)
Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 7)

Conclusión

Un modelo de datos con varias tablas de hechos necesita una única tabla calendario que las cubra todas. Hacerlo a ojo deja huecos o sobra. Hacerlo con MIN y MAX de todas las tablas te asegura que no falta ni sobra ni un día.

Mi regla: si tienes más de una tabla de hechos, calcula el calendario desde Power Query con el mínimo y el máximo de todas. Cada vez que actualices, el rango se ajusta solo.

Próximo paso: abre Power Query, crea una consulta nueva y escribe la fórmula que calcula el mínimo de tus tablas de hechos. Después haz lo mismo con el máximo. Genera el calendario entre esas dos fechas y carga la tabla al modelo.

Un calendario que cubre todas tus tablas de hechos (sin inventarte fechas ni dejar huecos) (diapositiva 8)

Únete a la conversación

¿Qué te ha parecido? Comparte tu experiencia o tu pregunta en los comentarios.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *



    DigitoCero © 2024 | Desarrollo web: cromo.com.es