Domina las Tablas Calendario con DAX en Power BI 📅
¡Crea una tabla calendario en Power BI con DAX y desbloquea el poder del Time Intelligence! Aprende a usar CALENDAR y CALENDARAUTO, configurar columnas, relacionar tablas y ordenar fechas correctamente. Descarga las tablas desde el enlace y optimiza tus reportes. 💻📊
Paso a Paso
Preparación: Importar datos 📥
Descarga el archivo de práctica (Excel) 3 Tablas Excel Relacionadas
.
En Power BI, ve a Inicio > Obtener Datos > Excel y carga las tablas:
- Empleados: ID, Nombre, Departamento.
- Productos: ID, NombreProducto, Precio.
- Ventas: ID, Fecha, IDEmpleado, IDProducto, Cantidad.
Verifica en Vista de Modelo: Confirma las relaciones (Ventas[IDProducto] con Productos[ID], Ventas[IDEmpleado] con Empleados[ID]). 🎉
Paso 1: Identificar un error sin tabla calendario 📉
Muestra un problema común al no usar una tabla calendario.
Crea una tabla para medidas:
- Ve a Inicio > Introducir Datos, crea una tabla llamada MedidasDAX y cárgala.
En MedidasDAX, haz clic derecho > Nueva Medida.
Nómbrala Suma Cantidades x Pro y escribe:
Suma Cantidad x Pro =
SUM(T_Ventas[Cantidad]
)
Crea visualizaciones:
- Inserta una Tabla: Arrastra NombreProducto (de Productos) y SumaCantidades.
- Inserta un Gráfico de Áreas:
- Eje X: Fecha (de Ventas), selecciona Día en la jerarquía.
- Valores: SumaCantidades.
- Inserta una Segmentación: Arrastra Fecha (de Ventas), selecciona Año y Mes.
Identifica el error:
- Selecciona 2023 > Enero en la segmentación.
- En el gráfico, ves días 10, 11, 15, pero faltan 12, 13, 14 (sin datos).
- Problema: La tabla Ventas no tiene esas fechas, causando una visualización engañosa.
- Solución: Una tabla calendario incluye todas las fechas, mostrando ceros para días sin datos. 🔍
Paso 2: Crear una tabla calendario con CALENDARAUTO 📆
Usa CALENDARAUTO para generar fechas automáticamente.
Ve a Inicio > Introducir Datos > Nueva Tabla.
Nómbrala Calendario y escribe:
Calendario = CALENDARAUTO()
- Explicación: Genera una lista de fechas continuas desde el 01/01 del año mínimo (2023) hasta el 31/12 del año máximo (2024) basado en todas las columnas de tipo fecha en el modelo.
Presiona Enter, verifica en Vista de Datos: La tabla tiene una columna Date con fechas del 01/01/2023 al 31/12/2024.
Configura la columna:
- Selecciona Date, asegura que el Tipo de Datos sea Fecha.
- Cambia el Formato (opcional) a un estilo claro, como
dd/mm/yyyy.
Nota: CALENDARAUTO puede incluir años no deseados (por ejemplo, fechas de nacimiento en Empleados). Si hay múltiples columnas de fecha, usa CALENDAR (Paso 3). ⚠️
Paso 3: Crear una tabla calendario con CALENDAR 🎯
Define fechas específicas con CALENDAR para mayor control.
Elimina la tabla Calendario anterior (haz clic en los 3 puntos > Eliminar del modelo).
Ve a Inicio > Introducir Datos > Nueva Tabla.
Nómbrala Calendario y escribe:
Calendario = CALENDAR(DATE(YEAR(MIN(T_Ventas[Fecha])),1,1),
DATE(YEAR(MAX(T_Ventas[Fecha])),12,31))- Explicación:
- MIN(Ventas[Fecha]): Fecha mínima (06/01/2023).
- MAX(Ventas[Fecha]): Fecha máxima (30/12/2024).
- DATE(YEAR(…), 1, 1): Establece el 01/01 del año mínimo (01/01/2023).
- DATE(YEAR(…), 12, 31): Establece el 31/12 del año máximo (31/12/2024).
- Genera fechas continuas desde 01/01/2023 hasta 31/12/2024.
Presiona Enter, verifica en Vista de Datos: Confirma fechas desde 01/01/2023 al 31/12/2024.
Configura la columna Date:
- Tipo de Datos: Fecha.
- Formato:
dd/mm/yyyy(o el deseado). 😄
Paso 4: Añadir columnas derivadas con DAX 📋
Agrega columnas para análisis temporal (año, mes, etc.).
Opción 1: Crear columnas manualmente
En Calendario, haz clic derecho > Nueva Columna.
Añade columnas como:
- Año:
DAX Año = YEAR(Calendario[Date]) - Mes (número):
DAX Mes# = MONTH(Calendario[Date]) - Mes (texto):
DAX Mes = FORMAT(Calendario[Date], "mmmm") - Día:
DAX Día# = DAY(Calendario[Date]) - Día de la semana:
DAX DíaSemana# = WEEKDAY(Calendario[Date], 2) // 1=Lunes, 7=Domingo - Nombre del día:
DAX DíaSemana = FORMAT(Calendario[Date], "dddd") - Trimestre:
DAX Trimestre# = QUARTER(Calendario[Date])
Opción 2: Usar script DAX predefinido
Descarga el script DAX Calendario .TXT ⬇️
Elimina la tabla Calendario anterior.
Ve a Inicio > Nueva Tabla, pega el script y ajusta:
- Cambia las referencias de la tabla y columna de fecha (por ejemplo,
Ventas[Fecha]) en las líneas de MIN y MAX. - Ejemplo de script (simplificado):
Calendario DAX = ADDCOLUMNS (
CALENDAR ( DATE( YEAR ( MIN (T_Ventas[Fecha])), 01, 01), DATE( YEAR( MAX(T_Ventas[Fecha]) ), 12, 31 ) ),"FechaSK", FORMAT ( [Date], "YYYYMMDD" ),
"#A o", YEAR ( [Date] ),
"#Trimestre", QUARTER ( [Date] ),
"#Mes", MONTH ( [Date] ),
"#D a", DAY ( [Date] ),
"Trimestre", "T" & FORMAT ( [Date], "Q" ),
"Mes", FORMAT ( [Date], "MMMM" ),
"MesCorto", FORMAT ( [Date], "MMM" ),
"#D aSemana", WEEKDAY ( [Date],2 ),
"#SemanaA o", WEEKNUM ( [Date],2 ),
"CierreSemana", ( [Date] + 7 - WEEKDAY( [Date],2 ) ),
"D a", FORMAT ( [Date], "DDDD" ),
"D aCorto", FORMAT ( [Date], "DDD" ),
"A oTrimestre", FORMAT ( [Date], "YYYY" ) & "/T" & FORMAT ( [Date], "Q" ),
"A o#Mes", FORMAT ( [Date], "YYYY/MM" ),
"A oMesCorto", FORMAT ( [Date], "YYYY/mmm" ),
"InicioMes", EOMONTH( [Date], -1) + 1,
"FinMes", EOMONTH( [Date], 0) )
Presiona Enter, verifica en Vista de Datos: Todas las columnas (Año, Mes#, Mes, etc.) están creadas.
Renombra la columna Date a Fecha: Haz doble clic y escribe Fecha.
Paso 5: Marcar como tabla de fechas ⚙️
Habilita funciones de Time Intelligence.
- En Vista de Datos, selecciona la tabla Calendario.
- Ve a la pestaña Tabla > Marcar como tabla de fechas.
- En el cuadro de diálogo, selecciona la columna Fecha como la columna de fecha.
- Haz clic en Guardar. 🌟
Paso 6: Relacionar la tabla calendario 🔗
Conecta la tabla calendario con Ventas.
- Ve a Vista de Modelo.
- Arrastra Fecha (de Calendario) a Fecha (de Ventas).
- Confirma la relación 1 a muchos (una fecha en Calendario puede tener múltiples ventas en Ventas)
- Oculta columnas innecesarias:
- En Ventas, haz clic en Fecha, IDEmpleado, IDProducto, ID > Ocultar de la vista de informes.
- Esto evita usar columnas de Ventas directamente en visualizaciones. 😎
Paso 7: Ordenar meses y configurar columnas 📑
Asegura un orden lógico y evita sumas automáticas.
Ordenar meses:
- En Vista de Datos, selecciona la columna Mes (texto).
- Ve a Columna > Ordenar por columna > Selecciona Mes#.
- Esto ordena Enero, Febrero, Marzo (no alfabéticamente como Abril, Enero).
Configurar columnas numéricas:
- Selecciona columnas como Año, Mes#, Día#, DíaSemana#, Trimestre#.
- En Propiedades, establece Resumir por a No resumir para evitar sumas automáticas en visualizaciones.
Paso 8: Corregir el gráfico de áreas 📈
Ajusta la medida para mostrar ceros en días sin datos.
Modifica la medida SumaCantidades:
- En MedidasDAX, haz clic en SumaCantidades y edita:
Suma Cantidad x Pro =IF(ISBLANK(SUM(T_Ventas[Cantidad])),0,SUM(T_Ventas[Cantidad]))
Explicación: Si la suma es vacía (sin datos), devuelve 0; si no, calcula la suma.
Paso 9: Validar la visualización 🖼️
Confirma que el gráfico refleja datos correctamente.
Cambia de mes en la segmentación (por ejemplo, Febrero, Diciembre).
Observa:
- Meses con datos muestran valores correctos.
- Meses sin datos (como Diciembre) muestran ceros.
- El gráfico de áreas o columnas incluye todos los días, manteniendo consistencia visual.
Ajusta FechaSK si es necesario (por ejemplo, usa solo Día# y ordénalo por Fecha). 😄
Cierre 🎬
¡Increíble! 🎉 Ahora dominas la creación de tablas calendario con DAX usando CALENDAR y CALENDARAUTO, y sabes cómo relacionarlas, ordenarlas y corregir errores visuales. Practica con las tablas descargadas y prepárate para el próximo video, donde crearemos la misma tabla con Power Query. ¿Dudas? Déjalas en los comentarios. ¡Nos vemos en el próximo capítulo! 👋
Responses