Tabla de fechas del modelo de datos con un clic

Usar el modelo de datos (o Power Pivot) nos ayuda a crear algunas tablas dinámicas increíbles. Una tabla de fechas (o tabla de calendario) es parte integral de la mayoría de los modelos de datos, ya que nos permiten agrupar informes por varios períodos de fechas y utilizar una amplia variedad de funciones de inteligencia temporal. Aunque hay muchas formas de crear una tabla de fechas de este tipo, esta publicación demuestra cómo crear una haciendo clic en un solo icono de comando.

Video

He creado un vídeo corto junto con una narrativa completa para seguir los pasos.

Version corta

La respuesta corta es usar el comando Tabla de fechas Nuevo en la pestaña de la cinta Diseño de Power Pivot.

Nota: el comando Tabla de fechas Nuevo está disponible en la versión actual de Excel y, según la versión de Excel que esté utilizando, es posible que no tenga este comando disponible.

Ahora, repasemos la respuesta larga y los pasos detallados a continuación.

Pasos detallados

Supongamos que tenemos un montón de transacciones en una tabla y queremos crear un informe de tabla dinámica que muestre los totales por día de la semana. Lo lograremos completando juntos los siguientes pasos:

• Insertar una tabla dinámica
• Crear tabla de fechas
• Definir relación
• Grupo por día de la semana

Insertar una tabla dinámica

Primero, necesitamos cargar las transacciones en el modelo de datos y configurar una tabla dinámica básica. Las primeras filas de nuestra tabla Transacciones se muestran a continuación.

Para insertar una tabla dinámica Y cargar la tabla de transacciones en el modelo de datos en un solo paso, seleccione cualquier celda de la tabla y seleccione:

• Insertar Tabla dinámica

Excel muestra el cuadro de diálogo Crear tabla dinámica, como se muestra a continuación.

Asegúrese de marcar la casilla de verificación “Agregar estos datos al modelo de datos” y haga clic en Aceptar.

Si quisiéramos hacer algo simple, como ver los totales mensuales, simplemente podríamos arrastrar el campo Fecha al área Filas y el campo Monto al área Valores. La tabla dinámica resultante se muestra a continuación.

Pero, cuando los requisitos de nuestros informes son más complejos, por ejemplo, agruparlos por día de la semana o usar funciones de inteligencia de tiempo, necesitaremos agregar una tabla de fecha/calendario al modelo de datos.

Agregar tabla de fechas

Para agregar una tabla de fechas, necesitaremos abrir la ventana de Power Pivot. Una forma de hacerlo es haciendo clic en el comando Datos Administrar modelo de datos como se muestra a continuación.

Nota: dependiendo de la resolución de su pantalla, es posible que no vea la etiqueta y en su lugar solo vea el ícono

Si no ha activado previamente el complemento Power Pivot, probablemente verá este cuadro de diálogo:

Si es así, simplemente haga clic en Habilitar para continuar.

Nota: también puede activar el complemento manualmente yendo a Opciones de Excel Administrar complementos COM Microsoft Power Pivot para Excel.

Ahora que la ventana de Power Pivot está abierta, insertamos una tabla de fechas haciendo clic en el siguiente icono de comando:

• Diseño Tabla de fechas Nuevo

Excel inserta una nueva tabla de fechas, cuyas primeras filas se muestran a continuación.

Nota: el comando Tabla de fechas Nuevo está disponible en la versión actual de Excel y, según la versión de Excel que esté utilizando, es posible que no tenga este comando disponible.

El modelo de datos ahora tiene la tabla Transacciones y la nueva tabla de fechas denominada Calendario.

Es importante señalar que esta tabla de fechas básica es sólo el comienzo. Si es necesario, podemos agregar una variedad de columnas calculadas a la tabla de fechas, o incluso cambiar el rango de fechas usando:

• Tabla de fechas Rango de actualización

Antes de salir de la ventana de Power Pivot, debemos definir la relación.

Definir relación

Necesitamos decirle a Excel que nuestras dos tablas están relacionadas entre sí por la columna de fecha. Hay varias maneras de construir esta relación. Una forma es alternar a la vista de diagrama haciendo clic en Inicio Vista de diagrama . Una vez aquí, simplemente hacemos clic y arrastramos el campo Fecha desde la tabla Transacciones al campo Fecha en la tabla Calendario. La relación se crea, como se muestra a continuación.

Estamos en buena forma en este momento y ahora podemos actualizar la tabla dinámica. Para volver a Excel, podemos simplemente hacer clic en Archivo Cerrar (o en el pequeño icono de Excel).

Grupo por día de la semana

Ahora somos libres de utilizar cualquiera de los campos de la tabla Calendario con nuestra tabla dinámica. Por ejemplo, si queremos agrupar por día de la semana en lugar de por meses, simplemente podemos eliminar todos los campos de Fila actuales y luego insertar el campo Día de la semana de la tabla Calendario. El informe actualizado se muestra a continuación.

Nota: si no ve la tabla Calendario en el panel Campos de tabla dinámica, haga clic en la etiqueta Todo para mostrar todas las tablas en el modelo de datos.

Sí… ¡construimos el informe deseado!

Gracias Microsoft por este regalo de comando de tabla de fechas

No dude en descargar el archivo de muestra a continuación en caso de que desee comprobarlo. Y, si tiene otros consejos divertidos sobre Power Pivot, compártalos publicando un comentario a continuación.

Archivo de muestra

• Tabla de fechas después (completa)
• DateTable antes (solo datos)