Iniciar sesión con Microsoft
Iniciar sesión o crear una cuenta
Hola:
Seleccione una cuenta diferente.
Tiene varias cuentas
Elija la cuenta con la que desea iniciar sesión.

Las tablas de fecha en Power Pivot son esenciales para explorar y calcular datos a lo largo del tiempo. Este artículo proporciona una comprensión exhaustiva de las tablas de fechas y cómo puede crearlas en Power Pivot. En particular, en este artículo se describen:

  • Por qué una tabla de fechas es importante para examinar y calcular datos por fechas y hora.

  • Cómo usar Power Pivot para agregar una tabla de fechas al modelo de datos.

  • Cómo crear columnas de fecha nuevas como Año, Mes y Punto en una tabla de fechas.

  • Cómo crear relaciones entre tablas de fecha y tablas de hechos.

  • Cómo trabajar con el tiempo.

Este artículo está destinado a usuarios nuevos en Power Pivot. Sin embargo, es importante tener una buena comprensión de importar datos, crear relaciones y crear columnas y medidas calculadas.

En este artículo no se describe cómo usar DAX Time-Intelligence funciones en fórmulas de medida. Para obtener más información sobre cómo crear medidas con funciones de inteligencia de tiempo de DAX, vea Inteligencia de tiempo en Power Pivot en Excel.

Nota: En Power Pivot, los nombres "medida" y "campo calculado" son sinónimos. Estamos usando la medida de nombre en este artículo. Para obtener más información, vea Medidas en Power Pivot.

Contenido

Descripción de las tablas de fechas

Casi todos los análisis de datos implican examinar y comparar datos a lo largo de fechas y horas. Por ejemplo, es posible que desee sumar los importes de ventas del último trimestre fiscal y, a continuación, comparar esos totales con otros trimestres, o puede que desee calcular un saldo de cierre de fin de mes para una cuenta. En cada uno de estos casos, usa las fechas como forma de agrupar y agregar transacciones de ventas o saldos durante un período de tiempo determinado.

Power View informe

Tabla dinámica de ventas totales por trimestre fiscal

Una tabla de fechas puede contener muchas representaciones diferentes de fechas y horas. Por ejemplo, una tabla de fechas a menudo tendrá columnas como Año fiscal, Mes, Trimestre o Período que puede seleccionar como campos de una lista de campos al cortar y filtrar los datos en tablas dinámicas o Power View informes.

Power View lista de campos

Lista de campos de Power View

Para que las columnas de fecha como Año, Mes y Trimestre incluyan todas las fechas dentro de su intervalo respectivo, la tabla de fechas debe tener al menos una columna con un conjunto contiguo de fechas. Es decir, esa columna debe tener una fila por cada día de cada año incluida en la tabla de fechas.

Por ejemplo, si los datos que desea examinar tienen fechas entre el 1 de febrero de 2010 y el 30 de noviembre de 2012 e informa sobre un año natural, querrá una tabla de fechas con al menos un intervalo de fechas entre el 1 de enero de 2010 y el 31 de diciembre de 2012. Cada año en la tabla de fechas debe contener todos los días de cada año. Si va a actualizar periódicamente los datos con datos más recientes, es posible que desee ejecutar la fecha de finalización en uno o dos años, por lo que no tiene que actualizar la tabla de fechas conforme pasa el tiempo.

Tabla de fechas con un conjunto contiguo de fechas

Tabla de fechas con fechas contiguas

Si informa sobre un año fiscal, puede crear una tabla de fechas con un conjunto contiguo de fechas para cada año fiscal. Por ejemplo, si el año fiscal comienza el 1 de marzo y tiene datos para los años fiscales 2010 hasta la fecha actual (por ejemplo, en el ejercicio fiscal 2013), puede crear una tabla de fechas que comience el 1/3/2009 e incluya al menos todos los días de cada año fiscal hasta la última fecha del año fiscal 2013.

Si va a informar sobre el año natural y el año fiscal, no es necesario crear tablas de fechas separadas. Una sola tabla de fechas puede incluir columnas para un año natural, un año fiscal e incluso un calendario de trece períodos de cuatro semanas. Lo importante es que la tabla de fechas contiene un conjunto contiguo de fechas para todos los años incluidos.

Agregar una tabla de fechas al modelo de datos

Hay varias formas de agregar una tabla de fechas al modelo de datos:

  • Importar desde una base de datos relacional u otro origen de datos.

  • Cree una tabla de fecha en Excel y, a continuación, copie o vincule a una nueva tabla en Power Pivot.

  • Importe desde Microsoft Azure Marketplace.

Veamos cada uno de estos más de cerca.

Importar desde una base de datos relacional

Si importa algunos o todos los datos de un almacén de datos u otro tipo de base de datos relacional, es probable que ya haya una tabla de fechas y relaciones entre ellos y el resto de los datos que va a importar. Es probable que las fechas y el formato coincidan con las fechas de los datos de hecho, y es probable que las fechas comiencen bien en el pasado y vayan muy lejos en el futuro. La tabla de fechas que desea importar puede ser muy grande y contener un rango de fechas más allá de lo que tendrá que incluir en el modelo de datos. Puede usar las características avanzadas de filtro del Asistente para importación de tablas de Power Pivot para elegir selectivamente solo las fechas y las columnas concretas que realmente necesita. Esto puede reducir significativamente el tamaño del libro y mejorar el rendimiento.

Asistente para importar tablas

Cuadro de diálogo del Asistente para la importación de tablas

En la mayoría de los casos, no tendrá que crear columnas adicionales como Año fiscal, Semana, Nombre del mes, etc. porque ya existirán en la tabla importada. Sin embargo, en algunos casos, después de importar la tabla de fechas al modelo de datos, es posible que tenga que crear columnas de fecha adicionales, según una necesidad de informes determinada. Afortunadamente, esto es fácil de hacer con DAX. Más adelante, aprenderá a crear campos de tabla de fechas. Cada entorno es diferente. Si no está seguro de si los orígenes de datos tienen una tabla de calendario o fecha relacionada, hable con el administrador de la base de datos.

Crear una tabla de fecha en Excel

Puede crear una tabla de fecha en Excel y, a continuación, copiarla en una tabla nueva en el modelo de datos. Esto es muy fácil de hacer y le ofrece una gran flexibilidad.

Al crear una tabla de fechas en Excel, comienza con una sola columna con un rango contiguo de fechas. A continuación, puede crear columnas adicionales como Año, Trimestre, Mes, Año fiscal, Período, etc. en la hoja de cálculo de Excel mediante fórmulas Excel o, después de copiar la tabla en el modelo de datos, puede crearlas como columnas calculadas. La creación de columnas de fecha adicionales en Power Pivot se describe en la sección Agregar nuevas columnas de fecha a la tabla de fechas más adelante en este artículo.

Cómo: Crear una tabla de fecha en Excel copiarla en el modelo de datos

  1. En Excel, en una hoja de cálculo en blanco, en la celda A1,escriba un nombre de encabezado de columna para identificar un rango de fechas. Normalmente, esto será algo parecido a Date, DateTime o DateKey.

  2. En la celda A2,escriba una fecha de comienzo. Por ejemplo, 1/1/2010.

  3. Haga clic en el controlador de relleno y arrástrelo hacia abajo hasta un número de fila que incluya una fecha de finalización. Por ejemplo, 12/31/2016.

    Columna de fecha en Excel

  4. Seleccione todas las filas de la columna Fecha (incluido el nombre del encabezado en la celda A1).

  5. En el grupo Estilos, haga clic en Formato como tablay, a continuación, seleccione un estilo.

  6. En el cuadro de diálogo Formato como tabla, haga clic en Aceptar.

    Columna Fecha en Power Pivot

  7. Copie todas las filas, incluido el encabezado.

  8. En Power Pivot, en la pestaña Inicio, haga clic en Pegar.

  9. En Vista previa de > nombre de tabla escriba un nombre como Fecha o Calendario. Deje la casilla Usar la primera fila como encabezados de columna y, a continuación, haga clic en Aceptar.

    Vista previa de pegado

    La nueva tabla de fechas (denominada Calendario en este ejemplo) en Power Pivot tiene este aspecto:

    Tabla de fechas en Power Pivot

    Nota: También puede crear una tabla vinculada mediante Agregar al modelo de datos. Sin embargo, esto hace que el libro sea innecesariamente grande porque el libro tiene dos versiones de la tabla de fechas; uno en Excel y otro en Power Pivot.

Nota: La fecha de nombre es una palabra clave en Power Pivot. Si le asigne un nombre a la tabla que cree en Fecha de Power Pivot, tendrá que escribir el nombre de tabla entre comillas simples en cualquier fórmula de DAX que haga referencia a ella en un argumento. Todas las imágenes y fórmulas de ejemplo de este artículo hacen referencia a una tabla de fechas creada en Power Pivot denominada Calendario.

Ahora tiene una tabla de fechas en el modelo de datos. Puede agregar columnas de fecha nuevas como Año, Mes, etc. con DAX.

Agregar nuevas columnas de fecha a la tabla de fechas

Una tabla de fechas con una única columna de fecha que tiene una fila para cada día para cada año es importante para definir todas las fechas de un intervalo de fechas. También es necesario para crear una relación entre la tabla de hechos y la tabla de fechas. Pero esa columna de fecha única con una fila para cada día no es útil al analizar por fechas en una tabla dinámica o Power View informe. Desea que la tabla de fechas incluya columnas que le ayuden a agregar los datos de un rango o grupo de fechas. Por ejemplo, es posible que desee sumar importes de ventas por mes o trimestre, o puede crear una medida que calcule el crecimiento interanual. En cada uno de estos casos, la tabla de fechas necesita columnas de año, mes o trimestre que le permitan agregar los datos para ese período.

Si importó la tabla de fechas de un origen de datos relacional, es posible que ya incluya los distintos tipos de columnas de fecha que desee. En algunos casos, es posible que desee modificar algunas de esas columnas o crear columnas de fecha adicionales. Esto es especialmente cierto si crea su propia tabla de fechas en Excel y la copia en el modelo de datos. Afortunadamente, crear nuevas columnas de fecha en Power Pivot es bastante fácil con las funciones de fecha y hora en DAX.

Sugerencia: Si aún no ha trabajado con DAX, un buen lugar para empezar a aprender es con Inicio rápido: Aprenda los conceptos básicos de DAX en 30 minutos en Office.com.

Funciones de fecha y hora de DAX

Si alguna vez ha trabajado con funciones de fecha y hora en Excel fórmulas, es probable que esté familiarizado con las Funciones de fecha y hora. Aunque estas funciones son similares a sus equivalentes en Excel, hay algunas diferencias importantes:

  • Las funciones fecha y hora de DAX usan un tipo de datos datetime.

  • Pueden tomar valores de una columna como argumento.

  • Se pueden usar para devolver o manipular valores de fecha.

Estas funciones se usan a menudo al crear columnas de fecha personalizadas en una tabla de fechas, por lo que son importantes para comprender. Usaremos una serie de estas funciones para crear columnas para Año, Trimestre, FiscalMonth, y así sucesivamente.

Nota: Las funciones de fecha y hora en DAX no son las mismas que las funciones de inteligencia de tiempo. Obtenga más información sobre inteligencia de tiempo en Power Pivot en Excel 2013.

DAX incluye las siguientes funciones de fecha y hora:

Hay muchas otras funciones de DAX que también puede usar en las fórmulas. Por ejemplo, muchas de las fórmulas que se describen aquí usan Funciones matemáticas y trigonométricas como MOD y TRUNC,Funciones lógicas como SIy Funciones de texto como FORMATO Para obtener más información sobre otras funciones de DAX, vea la sección Recursos adicionales más adelante en este artículo.

Ejemplos de fórmulas para un año calendario

En los ejemplos siguientes se describen fórmulas usadas para crear columnas adicionales en una tabla de fechas denominada Calendario. Una columna, denominada Fecha, ya existe y contiene un rango contiguo de fechas del 1/1/2010 al 12/31/2016.

Año

=AÑO([fecha])

En esta fórmula, la función AÑO devuelve el año del valor de la columna Fecha. Dado que el valor de la columna Fecha es del tipo de datos datetime, la función AÑO sabe cómo devolver el año.

Columna Año

Mes

=MES([fecha])

En esta fórmula, al igual que con la función AÑO, simplemente podemos usar la función MES para devolver un valor de mes de la columna Fecha.

Columna Mes

Trimestre

=INT(([Mes]+2)/3)

En esta fórmula, usamos la función INT para devolver un valor de fecha como un entero. El argumento que especificamos para la función INT es el valor de la columna Mes, agregue 2 y, a continuación, divida entre 3 para obtener nuestro trimestre, de 1 a 4.

Columna Trimestre

Nombre del mes

=FORMATO([fecha],"mmmm")

En esta fórmula, para obtener el nombre del mes, usamos la función FORMATO para convertir un valor numérico de la columna Fecha en texto. Especificamos la columna Fecha como primer argumento y, a continuación, el formato; queremos que nuestro nombre de mes muestre todos los caracteres, por lo que usamos "mmmm". Nuestro resultado es similar a este:

Columna Nombre del mes

Si queremos devolver el nombre del mes abreviado a tres letras, usaríamos "mmm" en el argumento de formato.

Día de la semana

=FORMATO([fecha],"ddd")

En esta fórmula, usamos la función FORMATO para obtener el nombre del día. Como solo queremos un nombre de día abreviado, especificamos "ddd" en el argumento de formato.

Columna Día de la semana
Ejemplo de tabla dinámica

Una vez que tenga campos para fechas como Año, Trimestre, Mes, etc., puede usarlos en una tabla dinámica o informe. Por ejemplo, la siguiente imagen muestra el campo SalesAmount de la tabla de hechos Ventas en VALORES y Año y Trimestre de la tabla de dimensiones Calendario en FILAS. SalesAmount se agrega para el contexto de año y trimestre.

Ejemplo de tabla dinámica

Ejemplos de fórmulas para un año fiscal

Año fiscal

=SI([Mes]<= 6,[Año],[Año]+1)

En este ejemplo, el año fiscal comienza el 1 de julio.

No hay ninguna función que pueda extraer un año fiscal de un valor de fecha porque las fechas de inicio y finalización de un año fiscal suelen ser diferentes de las de un año calendario. Para obtener el año fiscal, primero usamos una función SI para comprobar si el valor de Mes es menor o igual que 6. En el segundo argumento, si el valor de Mes es menor o igual que 6, devuelva el valor de la columna Año. Si no es así, devuelva el valor de Año y agregue 1.

Columna Año fiscal

Otra forma de especificar un valor de mes final del año fiscal es crear una medida que simplemente especifique el mes. Por ejemplo, FYE:=6. A continuación, puede hacer referencia al nombre de la medida en lugar del número de mes. Por ejemplo, =SI([Mes]<=[FYE],[Año],[Año]+1). Esto proporciona más flexibilidad al hacer referencia al mes de finalización del año fiscal en varias fórmulasdiferentes.

Mes fiscal

=SI([Mes]<= 6, 6+[Mes], [Mes]- 6)

En esta fórmula, especificamos si el valor de [Mes] es menor o igual que 6, después tome 6 y agregue el valor de Mes, de lo contrario resta 6 del valor de [Mes].

Columna Mes fiscal

Trimestre fiscal

=INT(([FiscalMonth]+2)/3)

La fórmula que usamos para FiscalQuarter es muy similar a la de Trimestre de nuestro año natural. La única diferencia es que especificamos [FiscalMonth] en lugar de [Mes].

Columna Trimestre Fiscal

Días festivos o fechas especiales

Es posible que desee incluir una columna de fecha que indique que determinadas fechas son días festivos o alguna otra fecha especial. Por ejemplo, es posible que desee sumar los totales de ventas para año nuevo agregando un campo De vacaciones a una tabla dinámica, como segmentación de datos o filtro. En otros casos, es posible que desee excluir esas fechas de otras columnas de fecha o en una medida.

Incluir días festivos o días especiales es muy sencillo. Puede crear una tabla en Excel que tenga las fechas que desea incluir. A continuación, puede copiar o usar Agregar al modelo de datos para agregarlo al modelo de datos como una tabla vinculada. En la mayoría de los casos, no es necesario crear una relación entre la tabla y la tabla Calendario. Cualquier fórmula que haga referencia a ella puede usar la función VALOR.BUSCAR para devolver valores.

A continuación se muestra un ejemplo de una tabla creada en Excel que incluye días festivos que se agregarán a la tabla de fechas:

Fecha

Días no laborables

1/1/2010

Años nuevos

11/25/2010

Acción de gracias

12/25/2010

Navidad

1/1/2011

Años nuevos

11/24/2011

Acción de gracias

12/25/2011

Navidad

1/1/2012

Años nuevos

22/11/2012

Acción de gracias

12/25/2012

Navidad

1/1/2013

Años nuevos

11/28/2013

Acción de gracias

12/25/2013

Navidad

11/27/2014

Acción de gracias

12/25/2014

Navidad

01/01/2014

Años nuevos

11/27/2014

Acción de gracias

12/25/2014

Navidad

1/1/2015

Años nuevos

11/26/2014

Acción de gracias

12/25/2015

Navidad

01/01/2016

Años nuevos

11/24/2016

Acción de gracias

12/25/2016

Navidad

En la tabla de fechas, creamos una columna denominada Vacaciones y usamos una fórmula como esta:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Veamos esta fórmula con más cuidado.

Usamos la función VALOR.BUSCAR para obtener valores de la columna Vacaciones en la tabla Días festivos. En el primer argumento, especificamos la columna donde estará nuestro valor de resultado. Especificamos la columna Vacaciones en la tabla Días festivos porque ese es el valor que queremos devolver.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

A continuación, especificamos el segundo argumento, la columna de búsqueda que tiene las fechas que queremos buscar. Especificamos la columna Fecha en la tabla Días festivos, de esta forma:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Por último, especificamos la columna de nuestra tabla Calendario que tiene las fechas que queremos buscar en la tabla Vacaciones. Por supuesto, esta es la columna Fecha de la tabla Calendario.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

La columna Vacaciones devolverá el nombre de las vacaciones de cada fila que tenga un valor de fecha que coincida con una fecha de la tabla Días festivos.

Tabla Días festivos

Calendario personalizado: trece períodos de cuatro semanas

Algunas organizaciones, como el comercio minorista o el servicio de comida, suelen informar sobre diferentes períodos, como trece períodos de cuatro semanas. Con un calendario de trece períodos de cuatro semanas, cada período es de 28 días; por lo tanto, cada período contiene cuatro lunes, cuatro martes, cuatro miércoles, y así sucesivamente. Cada período contiene el mismo número de días y, por lo general, los días festivos estarán dentro del mismo período cada año. Puede elegir iniciar un período en cualquier día de la semana. Al igual que con las fechas de un calendario o año fiscal, puede usar DAX para crear columnas adicionales con fechas personalizadas.

En los ejemplos siguientes, el primer período completo comienza el primer domingo del año fiscal. En este caso, el año fiscal comienza el 7/1.

Semana

Este valor nos proporciona el número de semana a partir de la primera semana completa del año fiscal. En este ejemplo, la primera semana completa comienza el domingo, por lo que la primera semana completa del primer año fiscal de la tabla Calendario comienza realmente el 4/7/2010 y continúa hasta la última semana completa en la tabla Calendario. Aunque este valor en sí no es tan útil en el análisis, es necesario calcular para su uso en otras fórmulas de período de 28 días.

=INT([fecha]-40356)/7)

Veamos esta fórmula con más cuidado.

En primer lugar, creamos una fórmula que devuelve valores de la columna Fecha como un entero, como este:

=INT([fecha])

A continuación, queremos buscar el primer domingo del primer año fiscal. Vemos que es 7/4/2010.

Columna Semana

Ahora, reste 40356 (que es el entero del 27/6/2010, el último domingo del año fiscal anterior) de ese valor para obtener el número de días desde el inicio de los días en nuestra tabla Calendario, como este:

=INT([fecha]-40356)

A continuación, divida el resultado por 7 (días en una semana), de este modo:

=INT(([fecha]-40356)/7)

El resultado es similar a este:

Columna Semana

Período

El período de este calendario personalizado contiene 28 días y siempre comenzará un domingo. Esta columna devolverá el número del período que comienza con el primer domingo del primer año fiscal.

=INT(([Semana]+3)/4)

Veamos esta fórmula con más cuidado.

En primer lugar, creamos una fórmula que devuelve un valor de la columna Semana como un entero, como este:

=INT([Semana])

A continuación, agregue 3 a ese valor, de esta forma:

=INT([Semana]+3)

A continuación, divida el resultado en 4, de este modo:

=INT(([Semana]+3)/4)

El resultado es similar a este:

Columna Período

Año fiscal del período

Este valor devuelve el año fiscal de un período.

=INT(([Período]+12)/13)+2008

Veamos esta fórmula con más cuidado.

En primer lugar, creamos una fórmula que devuelve un valor de Punto y agrega 12:

= ([Período]+12)

Dividimos el resultado en 13, porque hay trece períodos de 28 días en el año fiscal:

=(([Período]+12)/13)

Agregamos 2010, porque ese es el primer año de la tabla:

=(([Período]+12)/13)+2010

Por último, usamos la función INT para quitar cualquier fracción del resultado y devolver un número entero, cuando se divide por 13, como este:

=INT(([Período]+12)/13)+2010

El resultado es similar a este:

Columna Año fiscal de período

Período en FiscalYear

Este valor devuelve el número de período, del 1 al 13, empezando por el primer período completo (que comienza el domingo) en cada año fiscal.

=SI(MOD([Período],13), MOD([Período],13),13)

Esta fórmula es un poco más compleja, por lo que la describiremos primero en un idioma que comprendamos mejor. Esta fórmula indica, divida el valor de [Período] entre 13 para obtener un número de período (1-13) en el año. Si ese número es 0, devuelva 13.

En primer lugar, creamos una fórmula que devuelve el resto del valor de Punto por 13. Podemos usar las funciones MOD (matemáticas y trigonométricas) como esta:

=MOD([Período],13)

Esto, en su mayoría, nos proporciona el resultado que queremos, excepto cuando el valor de Período es 0 porque esas fechas no están dentro del primer año fiscal, como en los primeros cinco días de nuestra tabla de fechas calendario de ejemplo. Podemos encargarse de esto con una función SI. En caso de que nuestro resultado sea 0, devolveremos 13, de este modo:

=SI(MOD([Período],13),MOD([Período],13),13)

El resultado es similar a este:

Columna Período en año fiscal

Ejemplo de tabla dinámica

La imagen siguiente muestra una tabla dinámica con el campo SalesAmount de la tabla de hechos Ventas en VALORES, y los campos PeriodFiscalYear y PeriodInFiscalYear de la tabla de dimensiones fecha calendario en FILAS. SalesAmount se agrega para el contexto por año fiscal y período de 28 días en el año fiscal.

Ejemplo de tabla dinámica de año fiscal

Relaciones

Después de crear una tabla de fechas en el modelo de datos, para empezar a examinar los datos en tablas dinámicas e informes y para agregar datos basados en las columnas de la tabla de dimensiones de fecha, debe crear una relación entre la tabla de hechos con los datos de transacción y la tabla de fechas.

Como necesita crear una relación basada en fechas, querrá asegurarse de crear esa relación entre columnas cuyos valores son del tipo de datos datetime (Date).

Por cada valor de fecha de la tabla de hechos, la columna de búsqueda relacionada de la tabla de fechas debe contener valores que coincidan. Por ejemplo, una fila (registro de transacción) en la tabla De hecho ventas con un valor de 8/15/2012 12:00 a.m. en la columna DateKey debe tener un valor correspondiente en la columna Fecha relacionada de la tabla fecha (denominada Calendario). Este es uno de los motivos más importantes por los que desea que la columna de fecha de la tabla de fechas contenga un rango contiguo de fechas que incluya cualquier fecha posible en la tabla de hechos.

Relaciones en la vista Diagrama

Nota: Aunque la columna de fecha de cada tabla debe tener el mismo tipo de datos (Fecha), el formato de cada columna no importa.

Nota: Si Power Pivot no le permite crear relaciones entre las dos tablas, es posible que los campos de fecha no almacenen la fecha y la hora en el mismo nivel de precisión. Según el formato de columna, los valores pueden tener el mismo aspecto, pero se almacenan de forma diferente. Obtenga más información sobre cómo trabajar con tiempo.

Nota: Evite usar claves sustitutas de enteros en las relaciones. Al importar datos de un origen de datos relacional, a menudo las columnas de fecha y hora se representan mediante una clave sustituta, que es una columna entera que se usa para representar una fecha única. En Power Pivot, debe evitar la creación de relaciones mediante claves de fecha y hora enteras y, en su lugar, usar columnas que contengan valores únicos con un tipo de datos de fecha. Aunque el uso de claves sustitutas se considera una práctica recomendada en almacenes de datos tradicionales, las claves enteras no son necesarias en Power Pivot y pueden dificultar agrupar valores en tablas dinámicas por períodos de fecha diferentes.

Si recibe un error de no coincidencia de tipo al intentar crear una relación, es probable que se deba a que la columna de la tabla de hechos no es del tipo de datos Fecha. Esto puede ocurrir cuando Power Pivot no puede convertir automáticamente una fecha (normalmente un tipo de datos de texto) en un tipo de datos de fecha. Todavía puede usar la columna en la tabla de hechos, pero tendrá que convertir los datos con una fórmula dax en una nueva columna calculada. Vea Convertir fechas de tipo de datos de texto en un tipo de datos de fecha más adelante en el apéndice.

Varias relaciones

En algunos casos, es posible que sea necesario crear varias relaciones o crear varias tablas de fechas. Por ejemplo, si hay varios campos de fecha en la tabla De hecho de ventas, como DateKey, ShipDate y ReturnDate, todos pueden tener relaciones con el campo Fecha en la tabla Fecha del calendario, pero solo uno de ellos puede ser una relación activa. En este caso, dado que DateKey representa la fecha de la transacción y, por lo tanto, la fecha más importante, esto serviría mejor como relación activa. Los demás tienen relaciones inactivas.

La siguiente tabla dinámica calcula las ventas totales por año fiscal y trimestre fiscal. Una medida denominada Ventas totales, con la fórmula Ventas totales:=SUMA([SalesAmount]), se coloca en VALORES y los campos FiscalYear y FiscalQuarter de la tabla Fecha calendario se colocan en FILAS.

Tabla dinámica de ventas totales por trimestre fiscal Lista de campos de tabla dinámica

Esta tabla dinámica directa funciona correctamente porque queremos sumar nuestras ventas totales en lafecha detransacción en DateKey. Nuestra medida Ventas totales usa las fechas en DateKey y se suma por año fiscal y trimestre fiscal porque hay una relación entre DateKey en la tabla Ventas y la columna Fecha en la tabla Fecha del calendario.

Relaciones inactivas

Pero, ¿qué sucede si queremos sumar nuestras ventas totales no por fecha de transacción, sino por fecha de envío? Necesitamos una relación entre la columna FechaDePedido de la tabla Ventas y la columna Fecha de la tabla Calendario. Si no creamos esa relación, nuestras agregaciones siempre se basan en la fecha de transacción. Sin embargo, podemos tener varias relaciones, aunque solo una puede estar activa y, como la fecha de transacción es la más importante, obtiene la relación activa con la tabla Calendario.

En este caso, FechaDeEnvío tiene una relación inactiva, por lo que cualquier fórmula de medida creada para agregar datos en función de las fechas de envío debe especificar la relación inactiva mediante la función USERELATIONSHIP.

Por ejemplo, dado que hay una relación inactiva entre la columna FechaDePedido en la tabla Ventas y la columna Fecha de la tabla Calendario, podemos crear una medida que sumar el total de ventas por fecha de envío. Usamos una fórmula como esta para especificar la relación que se va a usar:

Ventas totales por fecha de envío:=CALCULATE(SUMA(Ventas[SalesAmount]), USERELATIONSHIP(Ventas[FechaEnvío], Calendario[Fecha]))

Esta fórmula simplemente indica: Calcule una suma para SalesAmount, pero filtre mediante la relación entre la columna FechaDePedido de la tabla Ventas y la columna Fecha de la tabla Calendario.

Ahora, si creamos una tabla dinámica y colocamos la medida Ventas totales por fecha de envío en VALORES, y Año fiscal y Trimestre fiscal en FILAS, vemos el mismo total general, pero todos los demás importes de suma para el año fiscal y el trimestre fiscal son diferentes porque se basan en la fecha de envío y no en la fecha de transacción.

Tabla dinámica de ventas totales por fecha de envío Lista de campos de tabla dinámica

El uso de relaciones inactivas le permite usar solo una tabla de fechas, pero requiere que cualquier medida (como Ventas totales por fecha de envío), haga referencia a la relación inactiva en su fórmula. Hay otra alternativa, es decir, usar varias tablas de fechas.

Varias tablas de fecha

Otra forma de trabajar con varias columnas de fecha en la tabla de hechos es crear varias tablas de fecha y crear relaciones activas independientes entre ellas. Veamos de nuevo nuestro ejemplo de tabla Ventas. Tenemos tres columnas con fechas en las que es posible que deseemos agregar datos:

  • Un DateKey con la fecha de venta de cada transacción.

  • FechaDePedido: con la fecha y la hora en que los artículos vendidos se enviaron al cliente.

  • Un ReturnDate: con la fecha y la hora en que se recibió uno o varios elementos devueltos.

Recuerde que el campo DateKey con la fecha de transacción es lo más importante. Realizaremos la mayoría de nuestras agregaciones en función de estas fechas, por lo que sin duda desearemos una relación entre ella y la columna Fecha de la tabla Calendario. Si no queremos crear relaciones inactivas entre ShipDate y ReturnDate y el campo Fecha de la tabla Calendario, por lo que se necesitan fórmulas de medida especial, podemos crear tablas de fecha adicionales para fecha de envío y fecha de devolución. A continuación, podemos crear relaciones activas entre ellas.

Relaciones con varias tablas de fechas en la vista Diagrama

En este ejemplo, hemos creado otra tabla de fechas denominada ShipCalendar. Esto, por supuesto, también significa crear columnas de fecha adicionales y, como estas columnas de fecha están en una tabla de fechas diferente, queremos nombrarlas de forma que las diferencia de las mismas columnas de la tabla Calendario. Por ejemplo, hemos creado columnas denominadas ShipYear, ShipMonth, ShipQuarter, y así sucesivamente.

Si creamos nuestra tabla dinámica y colocamos nuestra medida Ventas totales en VALORES, y ShipFiscalYear y ShipFiscalQuarter en FILAS, vemos los mismos resultados que vimos cuando creamos una relación inactiva y un campo calculado Ventas totales especiales por fecha de envío.

Tabla dinámica de ventas totales por fecha de envío con calendario de envío Lista de campos de la tabla dinámica

Cada uno de estos enfoques requiere una cuidadosa consideración. Al usar varias relaciones con una sola tabla de fechas, es posible que tenga que crear medidas especiales que transiten relaciones inactivas mediante la función USERELATIONSHIP. Por otro lado, crear varias tablas de fecha puede resultar confuso en una lista de campos y, como tiene más tablas en el modelo de datos, necesitará más memoria. Experimente con lo que mejor le conste.

Propiedad Tabla de fecha

La propiedad Tabla de fecha establece los metadatos necesarios para que Time-Intelligence funciones como TOTALYTD, ANTERIORMONTH y DATESBETWEEN funcionen correctamente. Cuando se ejecuta un cálculo con una de estas funciones, el motor de fórmulas de Power Pivot sabe a dónde ir para obtener las fechas que necesita.

Advertencia: Si no se establece esta propiedad, es posible que las medidas que usan Time-Intelligence DAX no devuelvan resultados correctos.

Al establecer la propiedad Tabla de fechas, especifique una tabla de fechas y una columna de fecha del tipo de datos Fecha (fecha y hora) en ella.

Cuadro de diálogo Marcar como tabla de fechas

Cómo: Establecer la propiedad Tabla de fechas

  1. En la PowerPivot, seleccione la tabla Calendario.

  2. En la pestaña Diseño, haga clic en Marcar como tabla de fecha.

  3. En el cuadro de diálogo Marcar como tabla de fechas, seleccione una columna con valores únicos y el tipo de datos Fecha.

Trabajar con tiempo

Todos los valores de fecha con un tipo de datos Fecha en Excel o SQL Server son realmente un número. En ese número se incluyen dígitos que hacen referencia a una hora. En muchos casos, esa hora para cada fila es medianoche. Por ejemplo, si un campo DateTimeKey de una tabla de hechos de ventas tiene valores como 10/19/2010 12:00:00, esto significa que los valores se encuentran en el nivel de precisión del día. Si los valores de campo DateTimeKey tienen una hora incluida, por ejemplo, 10/19/2010 8:44:00, esto significa que los valores están al nivel de precisión de minutos. Los valores también pueden ser la precisión de nivel de hora o incluso el nivel de precisión de segundos. El nivel de precisión en el valor de hora tendrá un impacto significativo en la forma en que cree la tabla de fechas y las relaciones entre ella y la tabla de hechos.

Debe determinar si agregará los datos a un nivel de precisión de día o a un nivel de precisión de tiempo. En otras palabras, es posible que desee usar columnas en la tabla de fechas como Mañana, Tarde u Hora como campos de fecha de hora en las áreas Fila, Columna o Filtro de una tabla dinámica.

Nota: Los días son la unidad de tiempo más pequeña con la que pueden trabajar las funciones de inteligencia de tiempo de DAX. Si no necesita trabajar con valores de tiempo, debe reducir la precisión de los datos para usar días como unidad mínima.

Si tiene la intención de agregar los datos al nivel de tiempo, la tabla de fechas necesitará una columna de fecha con la hora incluida. De hecho, necesitará una columna de fecha con una fila por cada hora, o tal vez incluso cada minuto, de cada día, para cada año en el intervalo de fechas. Esto se debe a que, para crear una relación entre la columna DateTimeKey de la tabla de hechos y la columna de fecha de la tabla de fechas, debe tener valores que coincidan. Como puede imaginar, si incluye una gran cantidad de años, esto puede crear una tabla de fechas muy grande.

Sin embargo, en la mayoría de los casos, desea agregar los datos solo al día. En otras palabras, usará columnas como Año, Mes, Semana o Día de la Semana como campos en las áreas Fila, Columna o Filtro de una tabla dinámica. En este caso, la columna de fecha de la tabla de fechas solo necesita contener una fila por cada día de un año, como se describe anteriormente.

Si la columna de fecha incluye un nivel de tiempo de precisión, pero solo se agregará a un nivel de día, para crear la relación entre la tabla de hechos y la tabla de fechas, es posible que tenga que modificar la tabla de hechos creando una nueva columna que trunca los valores de la columna de fecha a un valor de día. En otras palabras, convierta un valor como 10/19/2010 8:44:00 a 10/19/2010 12:00:00 a.m. A continuación, puede crear la relación entre esta nueva columna y la columna de fecha en la tabla de fechas porque los valores coinciden.

Veamos un ejemplo. Esta imagen muestra una columna DateTimeKey en la tabla de hechos ventas. Todas las agregaciones de los datos de esta tabla solo deben estar en el nivel de día, mediante columnas de la tabla Fecha del calendario como Año, Mes, Trimestre, etc. La hora incluida en el valor no es relevante, solo la fecha real.

Columna ClaveFechaHora

Como no necesitamos analizar estos datos en el nivel de tiempo, no necesitamos la columna Fecha en la tabla Fecha del calendario para incluir una fila por cada hora y cada minuto de cada día de cada año. Por lo tanto, la columna Fecha de nuestra tabla de fechas tiene el siguiente aspecto:

Columna Fecha en Power Pivot

Para crear una relación entre la columna DateTimeKey de la tabla Ventas y la columna Fecha de la tabla Calendario, podemos crear una nueva columna calculada en la tabla De hecho ventas y usar la función TRUNC para truncar el valor de fecha y hora de la columna DateTimeKey en un valor de fecha que coincida con los valores de la columna Fecha de la tabla Calendario. Nuestra fórmula tiene el siguiente aspecto:

=TRUNC([DateTimeKey],0)

Esto nos proporciona una nueva columna (denominada DateKey) con la fecha de la columna DateTimeKey y una hora de 12:00:00 a.m. para cada fila:

Columna ClaveFecha

Ahora podemos crear una relación entre esta nueva columna (DateKey) y la columna Fecha de la tabla Calendario.

De forma similar, podemos crear una columna calculada en la tabla Ventas que reduzca la precisión de tiempo de la columna DateTimeKey al nivel de precisión de horas. En este caso, la función TRUNC no funcionará, pero aún podemos usar otras funciones de fecha y hora de DAX para extraer y volver a concatenar un nuevo valor a un nivel de precisión de hora. Podemos usar una fórmula como esta:

= FECHA (AÑO([DateTimeKey]), MES([DateTimeKey]), DIA([DateTimeKey]) ) + HORA (HORA([DateTimeKey]), 0, 0)

Nuestra nueva columna tiene el siguiente aspecto:

Columna ClaveFechaHora

Siempre que nuestra columna Fecha de la tabla de fechas tenga valores en el nivel de precisión de hora, podemos crear una relación entre ellos.

Hacer que las fechas se pueden hacer más utilizables

Muchas de las columnas de fecha que cree en la tabla de fechas son necesarias para otros campos, pero realmente no son tan útiles en el análisis. Por ejemplo, el campo DateKey de la tabla Ventas al que hemos hecho referencia y que se muestra a lo largo de este artículo es importante porque, para cada transacción, esa transacción se registra como que se produce en una fecha y hora determinadas. Sin embargo, desde un punto de vista de análisis e informes, no es tan útil porque no podemos usarlo como una fila, columna o campo de filtro en una tabla dinámica o informe.

De forma similar, en nuestro ejemplo, la columna Fecha de la tabla Calendario es muy útil, crítica de hecho, pero no puede usarla como dimensión en una tabla dinámica.

Para mantener las tablas y las columnas en ellas lo más útil posible, y para que las listas de campos de tabla dinámica o informe Power View sean más fáciles de navegar, es importante ocultar columnas innecesarias de las herramientas de cliente. Es posible que también quiera ocultar determinadas tablas. La tabla Días festivos que se muestra anteriormente contiene fechas navideñas que son importantes para determinadas columnas de la tabla Calendario, pero no puede usar las columnas Fecha y Vacaciones de la tabla Días festivos como campos en una tabla dinámica. Aquí de nuevo, para que las listas de campos sean más fáciles de navegar, puede ocultar toda la tabla Días festivos.

Otro aspecto importante de trabajar con fechas es nombrar convenciones. Puede nombrar tablas y columnas en Power Pivot lo que desee. Pero tenga en cuenta que, especialmente si va a compartir el libro con otros usuarios, una buena convención de nomenclatura facilita la identificación de tablas y fechas, no solo en listas de campos, sino también en Power Pivot y en fórmulas de DAX.

Después de tener una tabla de fechas en el modelo de datos, puede empezar a crear medidas que le ayudarán a sacar el máximo partido de los datos. Algunos pueden ser tan sencillos como sumar los totales de ventas del año en curso y otros pueden ser más complejos, donde necesita filtrar en un rango determinado de fechas únicas. Obtenga más información en Medidas en Funciones de Inteligencia de tiempoy Power Pivot.

Apéndice

Convertir fechas de tipo de datos de texto en un tipo de datos de fecha

En algunos casos, una tabla de hechos con datos de transacción puede contener fechas de tipo de datos de texto. Es decir, una fecha que aparece como 2012-12-04T11:47:09 no es en realidad una fecha en absoluto, o al menos no el tipo de fecha que Power Pivot puede comprender. En realidad, es solo texto que se lee como una fecha. Para crear una relación entre una columna de fecha en la tabla de hechos y una columna de fecha en una tabla de fechas, ambas columnas deben ser del tipo de datos Fecha.

Normalmente, cuando intenta cambiar el tipo de datos de una columna de fechas que son tipo de datos de texto a un tipo de datos de fecha, Power Pivot puede interpretar las fechas y convertirla en un tipo de datos de fecha verdadero automáticamente. Si Power Pivot no puede realizar una conversión de tipo de datos, recibirá un error de no coincidencia de tipo.

Sin embargo, puede convertir las fechas en un tipo de datos de fecha verdadero. Puede crear una nueva columna calculada y usar una fórmula de DAX para analizar el año, el mes, el día, la hora, etc. a partir de las cadenas de texto y, a continuación, concatenar de nuevo de forma que Power Pivot pueda leerse como una fecha verdadera.

En este ejemplo, hemos importado una tabla de hechos denominada Ventas en Power Pivot. Contiene una columna denominada DateTime. Los valores aparecen de la siguiente forma:

Columna FechaHora en una tabla de hechos.

Si vemos Tipo de datos en la pestaña Inicio del grupo Formato de Power Pivot, vemos que es tipo de datos Texto.

Tipo de datos en la cinta de opciones

No podemos crear una relación entre la columna DateTime y la columna Fecha en nuestra tabla de fechas porque los tipos de datos no coinciden. Si intentamos cambiar el tipo de datos a Fecha,aparece un error de no coincidencia de tipo:

Error de coincidencia

En este caso, Power Pivot no pudo convertir el tipo de datos de texto a fecha. Todavía podemos usar esta columna, pero para convertirla en un tipo de datos de fecha real, necesitamos crear una nueva columna que analice el texto y lo vuelva a crear en un valor Power Pivot puede convertir un tipo de datos Fecha.

Recuerde, en la sección Trabajar con tiempo anterior en este artículo; a menos que sea necesario que el análisis tenga un nivel de precisión de hora del día, debe convertir las fechas de la tabla de hechos en un nivel de precisión de día. Con esto en mente, queremos que los valores de nuestra nueva columna se ajusten al nivel de precisión del día (excluyendo la hora). Ambos podemos convertir los valores de la columna DateTime en un tipo de datos de fecha y quitar el nivel de precisión de tiempo con la fórmula siguiente:

=FECHA(IZQUIERDA([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Esto nos proporciona una nueva columna (en este caso, denominada Fecha). Power Pivot incluso detecta los valores para que sean fechas y establece automáticamente el tipo de datos en Fecha.

Columna Fecha en una tabla de hechos

Si queremos conservar el nivel de tiempo de precisión, simplemente ampliamos la fórmula para incluir las horas, minutos y segundos.

=FECHA(IZQUIERDA([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

HORA(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Ahora que tenemos una columna Fecha del tipo de datos Fecha, podemos crear una relación entre ella y una columna de fecha en una fecha.

Recursos adicionales

Fechas en Power Pivot

Cálculos de Power Pivot

Inicio rápido: Aprender los conceptos básicos de DAX en 30 minutos

Referencia de expresiones de análisis de datos

Centro de recursos de DAX

¿Necesita más ayuda?

¿Quiere más opciones?

Explore las ventajas de las suscripciones, examine los cursos de aprendizaje, aprenda a proteger su dispositivo y mucho más.

Las comunidades le ayudan a formular y responder preguntas, enviar comentarios y leer a expertos con conocimientos extensos.

¿Le ha sido útil esta información?

¿Cuál es tu grado de satisfacción con la calidad del lenguaje?
¿Qué ha afectado a su experiencia?
Si presiona Enviar, sus comentarios se usarán para mejorar los productos y servicios de Microsoft. El administrador de TI podrá recopilar estos datos. Declaración de privacidad.

¡Gracias por sus comentarios!

×